sp_cacheconfig

Description

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

Syntax

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

Parameters

cachename

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.

cache_size

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 | inmemory_storage

specifies the type of cache. inmemory_storage indicates you are creating a cache for an in-memory or relaxed-durability database.

strict | relaxed

specifies the cache replacement policy.

cache_partition

specifies the number of partitions to create in the cache.

instance_name

in cluster environments – name of the instance whose cache you are adjusting.

Examples

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"

Example 7

Creates an in-memory storage named pubs3_imdb:

sp_cacheconfig pubs_imdb, '500M', inmemory_storage

Example 8

In cluster environments – displays the cache for instance blade1:

sp_cacheconfig 'instance blade1'

Example 9

In cluster environments – sets the size of the Sales Cache size on blade1 to 100 megabytes:

sp_cacheconfig 'Sales Cache', '100M', 'instance blade1'

Example 10

In cluster environments – sets the size of the Sales Cache size on blade1 to 0 megabytes, effectively dropping the cache.

sp_cacheconfig 'Sales Cache', '0M', 'instance blade1'

Usage


Data cache memory


Creating cache for in-memory or relaxed durability databases


Changing existing caches


Using cache partitions


Dropping caches

Permissions

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

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_bindcache, sp_helpcache, sp_poolconfig, sp_unbindcache, sp_unbindcache_all