Creating a new cache

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:

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 Adaptive Server’s global memory. When the cache is created:

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.

NoteWhen 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.