Creates, configures, reconfigures, and drops data caches, and provides information about them.


sp_cacheconfig [cachename [ ,"cache_size[P|K|M|G]" ] 
	[,logonly | mixed ] [,strict | relaxed ] ]
	[, "cache_partition=[1|2|4|8|16|32|64]"]



is the name of the data cache to be created or configured. Cache names must be unique, and can be up to 30 characters long. A cache name does not have to be a valid Adaptive Server identifier, that is, it can contain spaces and other special characters.


is the size of the data cache to be created or, if the cache already exists, the new size of the data cache. The minimum size of a cache is 256 times the logical page size of the server. Size units can be specified with P for pages, K for kilobytes, M for megabytes, or G for gigabytes. The default is K. For megabytes and gigabytes, you can specify floating-point values. The cache size is in multiples of the logical page size.

logonly | mixed

specifies the type of cache.

strict | relaxed

specifies the cache replacement policy.


specifies the number of partitions to create in the cache. Each pool in the cache must be at least one fourth the logical page size.


Example 1

Creates the data cache pub_cache with 10MB of space. All space is in the default logical page size memory pool:

sp_cacheconfig pub_cache, "10M"

Example 2

Reports the current configuration of pub_cache and any memory pools in the cache:

sp_cacheconfig pub_cache

Example 3

Drops pub_cache at the next start of Adaptive Server:

sp_cacheconfig pub_cache, "0"

Example 4

Creates pub_log_cache and sets its type to logonly in a single step:

sp_cacheconfig pub_log_cache, "2000K", logonly

Example 5

The first command creates the cache pub_log_cache with the default type mixed. The second command changes its status to logonly. The resulting configuration is the same as that in example 4:

sp_cacheconfig pub_log_cache, "2000K"
sp_cacheconfig pub_log_cache, logonly

Example 6

Creates a cache and sets the size, type, replacement policy and number of cache partitions:

sp_cacheconfig 'newcache', '50M', mixed, strict, "cache_partition=2"


Data cache memory

Changing existing caches

Using cache partitions

Dropping caches


Only a System Administrator can execute sp_cacheconfig to change cache configurations. Any user can execute sp_cacheconfig to view cache configurations.

See also

System procedures sp_bindcache, sp_helpcache, sp_poolconfig, sp_unbindcache, sp_unbindcache_all