Consultor Eletrônico

Status: Verified

GOAL:

How does -SQLTempBuff use memory?

FACT(s) (Environment):

OpenEdge 10.0x
Progress 9.1E
Progress 9.1E 64-bit
Progress 9.1D
Progress 9.1D 64-bit

FIX:

In OpenEdge 10.0B and earlier, the -SQLTempBuff is a per connection reservation of memory. For example, If the database was started with -SQLTempBuff 40000 then each user connected to the _sqlsrv2 process would have 40 meg of memory as an upper limit (40000 * 1k). This memory is retained until the user logs off. The memory is used to allow queries submitted by the user a reasonable amount of memory to work with the resulting data. There are no explicit guidelines on how much memory should be allocated per user since that would be based on the query or queries the users are submitting.

In OpenEdge SQL 10.1A and later, there is a completely new, high performance temp data manager. The temp data manager is used to do sorting, as well as "dynamic indexing" and certain temporary data storage. As a result, 10.1A SQL is much more scalable, faster sorting, which incurs less IO and uses less disk space. To get this high performance; the new sort makes aggressive use of process memory automatically, without startup parameters or other user intervention.

In SQL, sorting is used for the SQL ORDER BY and GROUP BY clauses, and the DISTINCT operation, as well as certain data retrieval strategies. The query may not appear to require sorting but sorting may be part of an estimated, optimal data retrieval strategy. For example, when a query contains multiple OR'd predicates which select on index keys, and the estimated costs are in favor, SQL may use a data retrieval strategy of unioning the sets of rowid which satisfy those predicates and this requires sorting.

The new 10.1A sort applies the startup parameter -SQLTempBuff in a different manner than OpenEdge 10.0B and earlier. -SQLTempBuff, if specified, now defines the amount of memory to use as a data buffer for each Sort operation. Formerly, -SQLTempBuff specified a temp dat