Kbase 21112: Some Undocumented SQL92 Environment Variables
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/10/2008 |
|
Status: Verified
GOAL:
Extended SQL-92 engine parameters
GOAL:
What environment variables could be set for the _sqlsrv2 processes?
GOAL:
What environment variables could be set to control memory usage of _sqlsrv2?
FIX:
Following environment variables can be set on the server machine prior to starting database.
These environment variables are basically designed for sorting operations(e.g. table scan with ORDER BY clause) which require temp table allocation.
They should only be used when there are no alternatives ( in other words, avoid sorting if you can. e.g. using index)
The setting of them are query and data dependent. Setting BLOCKSIZE for example is dependent by the total width of the row being sorted.
SWAPSIZE will affect disk space. Disk space for these sorting files is
located in WRKDIR. Check the db .lg file to see the setting of WRKDIR.
If WRKDIR is not set, it defaults to where the database is located.
BLOCKSIZE and CACHESIZE affect memory allocation.
1) Size of MM memory buffer [unit = 1kb], controls the in-memory page pool size
if you get "no cache block" - increase this variable value, you are
constrained by available memory on the machine.
Default (1 Meg):
# TPE_MM_CACHESIZE=1000;export TPE_MM_CACHESIZE
Sample :
# TPE_MM_CACHESIZE=8000;export TPE_MM_CACHESIZE
2) Size of MM on disk backing store [unit = 1kb], controls size of the on-disk
swap file, its value is constrained by the available disk space (prior to 9.1D07, the max limit was 2Gig)
Default (500 Meg):
# TPE_MM_SWAPSIZE=500000;export TPE_MM_SWAPSIZE
Sample :
# TPE_MM_SWAPSIZE=1000000;export TPE_MM_SWAPSIZE
3) Size of MM datablocks (page size) [unit = 1kb], controls the page size of
the block, increase this if you get "row too big" during sorting.
The maximum recommended value is 32 .
Default (7168):
# TPE_MM_BLOCKSIZE=7;export TPE_MM_BLOCKSIZE
Sample :
# TPE_MM_BLOCKSIZE=10;export TPE_MM_BLOCKSIZE
Beware of keeping the relative ratios somewhat intact, as they do interrelate, as an example, making the BLOCKSIZE bigger than the CACHESIZE doesn't make sense.