SQL Optimization

So, yes the globals is used for caching data in memory, particularly where you want subsequent passes of a business rule to 'remember' some information that a previous pass has done.  Examples are:
- in a Finance BR, where you want to cache information say about legal ownership read from a register table. During consolidation, as it moves between different Cons members, entities and time periods, you can share cached information between the calculation of each data unit.  So reading a table once is faster/more efficient than having each data unit query the table each time.
- in a DynamicCalc, where the DynamicCalc formula is excuted (in parallel) for each cell that is about to be displayed in the cube view. You certainly wouldn't want to query tables on every cell.  I like the pub analogy ; it's more efficient for one person to buy a round of drinks for 10 people, as opposed to each person having their own beer tab.

In terms of persistence, the globals object persists (stays valid) in these cases
- between Finance calculations on each data unit
- between DynamicCalc executions on each cell in a cube view 
- some other cases
but there are some cases, where annoyingly you cannot use globals to pass data to/from another BR pass. One example is if you have an extender rule , which initiates a Finance BR , using BRApi.Utilities.ExecuteCustomFinanceBusinessRule( ).   Unfortunately this API ( ExecuteCustomFinanceBusinessRule ) supplies a null value ( nothing ) to the finance BR.  Which means you cannot use globals to share values between an Extender rule and a Finance rule.  That is a shame.

After consolidation process has completed, I don't know whether the globals is explicitly disposed of , or whether the framework relies on the .NET garbage collector to do it.  Whatever the case,  don't store huge amounts of information in globals. e.g. a Data Table or (better) a Dictionary of 1000 items willl likely be fine, but not several million items.

As for the question on deleting all keys, vs specific keys, what are you referring to?  Data Table Manager?