Use sp_cacheconfig to create and configure named data caches. When you first install Adaptive Server, it has a single cache named default data cache. To see information about caches, type:
sp_cacheconfig
The results of sp_cacheconfig look similar to:
Cache Name Status Type Config Value Run Value ------------------------- --------- -------- ------------ ------------ default data cache Active Default 0.00 Mb 59.44 Mb ------------ ------------ Total 0.00 Mb 59.44 Mb ====================================================================== Cache: default data cache, Status: Active, Type: Default Config Size: 0.00 Mb, Run Size: 59.44 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 -------- --------- ------------ ------------ ----------- 2 Kb 12174 Kb 0.00 Mb 59.44 Mb 10
Summary information for each cache is printed in a block at the top of the report, ending with a total size for all configured caches. For each cache, there is a block of information reporting the configuration for the memory pools in the cache.
The columns are:
Cache Name – gives the name of the cache.
Status – indicates whether the cache is active. Possible values are:
“Pend/Act” – the cache was just created and will be active after a restart.
“Active” – the cache is currently active.
“Pend/Del” – the cache is active, but will be deleted at the next restart of the server. The cache size was reset to 0 interactively. See “Dropping data caches” for more information.
Type – indicates whether the cache can store data and log pages (“Mixed”) or log pages only (“Log Only”). Only the default cache has the type “Default.” You cannot change the type of the default data cache or change the type of any other cache to “Default.”
Config Value – displays the size of the cache after the next restart of Adaptive Server. In the preceding example output, the default data cache has not been explicitly configured, so its size is 0.
Run Value – displays the size that Adaptive Server is currently using. For the default data cache, this size is always the amount of all data cache space that is not explicitly configured to another cache.
The second block of output begins with three lines of information that describe the cache. The first two lines repeat information from the summary block at the top. On the third line, “Config Replacement” and “Run Replacement” show the cache policy, which is either “strict LRU” or “relaxed LRU.” The run setting is the setting in effect; if the policy has been changed since the server was restarted, the config setting will be different from the run setting.
sp_cacheconfig then provides a row of information for each pool in the cache:
IO Size – shows the size of the buffers in the pool. The default size of the pool is the size of the server’s logical page. When you first configure a cache, all the space is assigned to the pool. Valid sizes are 2K, 4K, 8K, and 16K.
Wash Size – indicates the wash size for the pool. See“Changing the wash area for a memory pool” for more information.
Config Size and Run Size – display the configured size and the size currently in use. These values differ for the pool because you cannot explicitly configure its size. These may differ for other pools if you have tried to move space between them, and some of the space could not be freed.
Config Partition and Run Partition – display the configured number of cache partitions and the number of partitions currently in use. These may differ if you have changed the number of partitions since last restart.
APF Percent – displays the percentage of the pool that can hold unused buffers brought in by asynchronous prefetch.
A summary line prints the total size of the cache or caches displayed.
