The syntax for sp_cacheconfig is:
sp_cacheconfig [cachename [ ,"cache_size[P|K|M|G]" ] [,logonly | mixed ] [,strict | relaxed ] ] [, "cache_partition=[1|2|4|8|16|32|64]"]
Size units can be specified with:
P – pages (Adaptive Server logical page size)
K – kilobytes (default)
M – megabytes
G – gigabytes
See the Adaptive Server Reference Manual for a full description of the sp_cacheconfig syntax.
Maximum data cache size is limited only by the amount of memory available on your system. The memory required to create the new cache is taken from the Adaptive Server global memory. When the cache is created:
It has a default wash size.
The asynchronous prefetch size is set to the value of global async prefetch limit.
It has only the default buffer pool.
Use sp_poolconfig to reset these values.
To create a 10MB cache named pubs_cache:
sp_cacheconfig pubs_cache, "10M"
This command makes changes in the system tables and writes the new values to the configuration file. The cache is immediately active. Running sp_cacheconfig now yields:
sp_cacheconfigCache Name Status Type Config Value Run Value ------------------------------ --------- -------- ------------ ------------ default data cache Active Default 0.00 Mb 8.00 Mb pubs_cache Active Mixed 10.00 Mb 10.00 Mb ------------ ------------ Total 10.00 Mb 18.00 Mb ========================================================================== Cache: default data cache, Status: Active, Type: Default Config Size: 0.00 Mb, Run Size: 8.00 Mb Config Replacement: strict LRU, Run Replacement: strict LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- --------- ------------ ------------ ----------- 4 Kb 1636 Kb 0.00 Mb 8.00 Mb 10 ========================================================================== Cache: pubs_cache, Status: Active, Type: Mixed Config Size: 10.00 Mb, Run Size: 10.00 Mb Config Replacement: strict LRU, Run Replacement: strict LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- --------- ------------ ------------ ----------- 4 Kb 2048 Kb 0.00 Mb 10.00 Mb 10
The pubs_cache is now active, and all space is assigned to the smallest pool.
When you create a new cache, the additional memory you
specify is validated against max memory. If the
sum of total logical memory and additional memory
requested is greater than max memory, then Adaptive
Server issues an error and does not perform the changes.
You can create as many caches as you want without restarting Adaptive Server.
