You must explicitly configure the size of the default data cache because it is specified with an absolute value. Use sp_helpcache to see the amount of memory remaining that can be used for the cache. For example:
sp_helpcache
Cache Name Config Size Run Size Overhead ------------------------ ------------- ---------- ---------- default data cache 25.00 Mb 25.00 Mb 0.22 Mb pubs_cache 10.00 Mb 10.00 Mb 0.11 Mb pubs_log 31.25 Mb 31.25 Mb 0.27 Mb Memory Available For Memory Configured Named Caches To Named Caches -------------------- ---------------- 66.44 Mb 66.25 Mb ------------------ Cache Binding Information: ------------------ Cache Name Entity Name Type Index Name Status ---------- ---------------- ---------- ----------
To specify the absolute size of the default data cache, execute sp_cacheconfig with default data cache and a size value. This command sets the default data cache size to 25MB:
sp_cacheconfig "default data cache", "25M"
After a restart of the server, “Config Value” shows the value.
sp_cacheconfig
Cache Name Status Type Config Value Run Value ------------------------- --------- -------- ------------ ------------ default data cache Active Default 25.00 Mb 49.37 Mb pubs_cache Active Mixed 10.00 Mb 10.00 Mb ------------ ------------ Total 10.00 Mb 59.37 Mb ====================================================================== Cache: default data cache, Status: Active, Type: Default Config Size: 25.00 Mb, Run Size: 49.37 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 10110 Kb 00.00 Mb 49.37 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 -------- --------- ------------ ------------ ----------- 2 Kb 2048 Kb 0.00 Mb 10.00 Mb 10
You can change the size of the default data cache by using sp_cacheconfig. Any changes made to the size of the default data cache will not affect the size of any other cache. Similarly, once the size of the default data cache is specified, the configuration of other user-defined caches does not alter the size of the default data cache. When a user-defined cache is created, the memory is taken from max memory, without changing the size of the default data cache.
If you configure the default data cache and then reduce max
memory to a level that sets the total logical
memory value higher than the max memory value,
Adaptive Server will not start. Edit your configuration file to
increase the size of other caches and increase the values of configuration parameters
that require memory to create an environment in which total logical
memory is higher than max memory. See Chapter 18, “Configuring Memory,” for more information.
The default data cache and all user-defined caches are explicitly configured with an absolute value. In addition, many configuration parameters use memory. To maximize performance and avoid errors, set the value of max memory to a level high enough to accommodate all caches and all configuration parameters that use memory.
Adaptive Server issues a warning message if you set max memory to a value less than total logical memory.
