Creates a new named cache. This syntax is extended from that of nonclustered Adaptive Server. It provides an extra option to specify the instance name for the local configuration at the end of the syntax. If you do not specify the instance name, the configuration is global.
sp_cacheconfig "[cachename [,cache_size [P|K|M|G]" [,logonly | mixed ] [,strict | relaxed ] ] [, "cache_partition = [1|2|4|8|16|32|64]"] [, "instance instance_name"]
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 – specifies the type of cache.
strict | relaxed – specifies the cache replacement policy.
cache_partition – specifies the number of partitions to create in the cache.
Example assumptions The following examples assume a shared-disk cluster named MYCLUSTER, which contains two instances:
SALES_INSTANCE
HR_INSTANCE
Creating a named cache We can create a named cache log_sales of size 100M which is specific to instance SALES_INSTANCE. Executing sp_cacheconfig on instance SALES_INSTANCE displays this output:
sp_cacheconfig 'log_sales','100M','instance SALES_INSTANCE' go
Cache Name Status Type Config Value Run Value------------------------ ------ -------------- ------------ ------------default data cache Active Global,Default 0.00 Mb 8.00 Mb SALES_INSTANCE:log_sales Active Mixed 100.00 Mb 100.00 Mb ------------ ------------ Total 100.00 Mb 108.00 Mb ========================================================================== Cache: default data cache, Status: Active, Type: Global,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 -------- ------------- ------------ ------------ ----------- 2 Kb 1638 Kb 0.00 Mb .00 Mb 10 ========================================================================== Cache: SALES_INSTANCE:log_sales, Status: Active, Type: Mixed Config Size: 100.00 Mb, Run Size: 100.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 20480 Kb 0.00 Mb 100.00 Mb 10 (return status = 0)
By default, an isql connection has a cluster view. All instance-specific caches are displayed at any instance. For example, instance HR_INSTANCE displays information about cache log_sales, which is the instance-specific cache for SALES_INSTANCE. If you want HR_INSTANCE to see only the list of the local caches specific to this instance, and the global caches, set the system view to instance.
Executing sp_cacheconfig on instance HR_INSTANCE displays:
set system_view instance go
Cache Name Status Type Config Value Run Value ------------------ ------ -------------- ------------ ------------ default data cache Active Global,Default 0.00 Mb 8.00 Mb ------------ ------------ Total 0.00 Mb 8.00 Mb ========================================================================== Cache: default data cache, Status: Active, Type: Global,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 -------- ------------- ------------ ------------ ----------- 2 Kb 1638 Kb 0.00 Mb 8.00 Mb 10 (return status = 0)
Restricting the output of cache information to instance level To display caches at instance SALES_INSTANCE, execute:
sp_cacheconfig 'instance SALES_INSTANCE' go
Cache Name Status Type Config Value Run Value------------------------ ------ -------------- ------------ ------------ default data cache Active Global,Default 0.00 Mb 8.00 Mb SALES_INSTANCE:log_sales Active Mixed 100.00 Mb 100.00 Mb ------------ ------------ Total 100.00 Mb 108.00 Mb ========================================================================== Cache: default data cache, Status: Active, Type: Global,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 -------- ------------- ------------ ------------ ----------- 2 Kb 1638 Kb 0.00 Mb 8.00 Mb 10 ========================================================================== Cache: SALES_INSTANCE:log_sales, Status: Active, Type: Mixed Config Size: 100.00 Mb, Run Size: 100.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 20480 Kb 0.00 Mb 100.00 Mb 10 (return status = 0)
This output displays both local and global configurations of instance SALES_INSTANCE.
Querying the existence of named caches To find out whether a specified cache already exists. Executing sp_cacheconfig on instance SALES_INSTANCE displays this output:
Cache Name Status Type Config Value Run Value ------------------------ ------ ----- ------------ ------------ SALES_INSTANCE:log_sales Active Mixed 100.00 Mb 100.00 Mb ------------ ------------ Total 100.00 Mb 100.00 Mb ========================================================================== Cache: SALES_INSTANCE:log_sales, Status: Active, Type: Mixed Config Size: 100.00 Mb, Run Size: 100.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 20480 Kb 0.00 Mb 100.00 Mb 10 (return status = 0)
Using Adaptive Server syntax to create global caches This cache is created at all instances and memory is allocated at all instances for a global cache. To create global cache tempdb_cache, run sp_cacheconfig at instance SALES_INSTANCE:
sp_cacheconfig 'tempdb_cache', '100M' go
Cache Name Status Type Config Value Run Value ------------------------ ------ -------------- ------------ ------------ default data cache Active Global,Default 0.00 Mb 8.00 Mb tempdb_cache Active Global,Mixed 100.00 Mb 100.00 Mb SALES_INSTANCE:log_sales Active Mixed 100.00 Mb 100.00 Mb ------------ ------------Total 200.00 Mb 208.00 Mb ========================================================================== Cache: default data cache, Status: Active, Type: Global,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 -------- ------------- ------------ ------------ ----------- 2 Kb 1638 Kb 0.00 Mb 8.00 Mb 10 ========================================================================== Cache: tempdb_cache, Status: Active, Type: Global,Mixed Config Size: 100.00 Mb, Run Size: 100.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 20480 Kb 0.00 Mb 100.00 Mb 10 ========================================================================== Cache: SALES_INSTANCE:log_sales, Status: Active, Type: Mixed Config Size: 100.00 Mb, Run Size: 100.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 20480 Kb 0.00 Mb 100.00 Mb 10 (return status = 0)
Creating a named cache with a single global and multiple local configurations All cache operations can be executed from any instance. For example, to create a bigger named cache, tempdb_cache, at SALES_INSTANCE, we can connect to instance HR_INSTANCE and execute:
sp_cacheconfig 'tempdb_cache','150M', 'instance SALES_INSTANCE'
Executing sp_cacheconfig at instance SALES_INSTANCE displays:
Cache Name Status Type Config Value Run Value ------------------ ------ -------------- ------------ ------------ default data cache Active Global,Default 0.00 Mb 8.00 Mb tempdb_cache Active Global,Mixed 100.00 Mb 100.00 Mb SALES_INSTANCE:log_hr Active Mixed 150.00 Mb 150.00 Mb SALES_INSTANCE:tempdb_cache Active Mixed 150.00 Mb 150.00 Mb ------------ ------------ Total 350.00 Mb 408.00 Mb ========================================================================== Cache: default data cache, Status: Active, Type: Global,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 -------- ------------- ------------ ------------ ----------- 2 Kb 1638 Kb 0.00 Mb 8.00 Mb 10 ========================================================================== Cache: tempdb_cache, Status: Active, Type: Global,Mixed Config Size: 100.00 Mb, Run Size: 150.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 30720 Kb 0.00Mb 150.00 Mb 10 ========================================================================== Cache: SALES_INSTANCE:log_sales, Status: Active, Type: Mixed Config Size: 100.00 Mb, Run Size: 100.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 20480 Kb 0.00 Mb 100.00 Mb 10 ============================================================================ Cache: SALES_INSTANCE:tempdb_cache, Status: Active, Type: Mixed Config Size: 150.00 Mb, Run Size: 150.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 30720 Kb 0.00 Mb 150.00 Mb 10 (return status = 0)
The local configuration of the named cache tempdb_cache overrides
the global configuration.
For example, if you set the system view to cluster, Adaptive Server may display all the configurations for a named cache, and you should ignore run values of any configuration which is not valid at that instance. For example, SALES_INSTANCE has a valid local configuration, cache tempdb_cache. Therefore, you should ignore the run values for global configuration.
Similarly, HR_INSTANCE has a valid global configuration. Therefore you should ignore the run values for local configuration of temdb_cache that are related to SALES_INSTANCE at instance HR_INSTANCE.
Adding memory to an existing cache To add memory, use the syntax documented in the Reference Manual: Procedures. The additional memory you allocate is added to the Adaptive Server page size pool. For example, the smallest size for a pool is 2K in a server with a logical page size of 2K. If the cache is partitioned, the additional memory is divided equally among the partitions.
If adding memory to an existing global cache fails at
an instance, but succeeds at least one other instance, the server
treats the operation as successful at the cluster-wide level. It
is thus possible to have different run values for a global cache,
but a single configuration value for the cache. sp_cacheconfig shows
the run values of a global cache from the syscurconfigs entry
of the current instance.
To increase tempdb_cache size to 200MB, in instance HR_INSTANCE, execute the following. Executing sp_cacheconfig 'tempdb_cache' on instance HR_INSTANCE displays this output:
sp_cacheconfig 'tempdb_cache','200M'
Cache Name Status Type Config Value Run Value ------------ ------ ------------ ------------ ------------ tempdb_cache Active Global,Mixed 200.00 Mb 200.00 Mb ------------ ------------ Total 200.00 Mb 200.00 Mb ========================================================================== Cache: tempdb_cache, Status: Active, Type: Global,Mixed Config Size: 200.00 Mb, Run Size: 200.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 40960 Kb 0.00 Mb 200.00 Mb 10 (return status = 0)
You can also increase the cache size of a local cache using the instance option documented on “sp_cacheconfig”.
Allocating space for a new cache If Adaptive Server cannot allocate the amount of memory you request, it allocates all available memory and issues an error message, telling you how many kilobytes have been allocated dynamically.
However, this memory is not allocated until you restart Adaptive Server. Adaptive Server notifies you of insufficient space, either because memory is unavailable, or because of resource constraints, which system administrators should ensure are temporary. If this behavior persists, a subsequent restart may fail.
For example, if the maximum memory is 700MB, tempdb_cache is 100MB, making the server’s total logical memory 600MB, and you attempt to add 100MB to tempdb_cache, the additional memory fits into maximum memory. However, if the server can allocate only 90MB, it allocates this amount dynamically, but the size field of the cache in the configuration file is updated to 100MB. On a subsequent restart, since Adaptive Server obtains memory for all data caches at once, the size of pub_cache is 100MB.
Decreasing a cache When you reduce a cache size, restart Adaptive Server. For example, to decrease the size of tempdb_cache to 100M, use the following. Executing sp_cacheconfig 'tempdb_cache' on instance HR_INSTANCE displays:
sp_cacheconfig 'tempdb_cache', '100M'
go
Cache Name Status Type Config Value Run Value ------------ ------ ------------ ------------ ------------ tempdb_cache Active Global,Mixed 100.00 Mb 200.00 Mb ------------ ------------ Total 100.00 Mb 200.00 Mb
========================================================================== Cache: tempdb_cache, Status: Active, Type: Global,Mixed Config Size: 100.00 Mb, Run Size: 200.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 40960 Kb 0.00 Mb 200.00 Mb 10 (return status = 0)
After restarting Adaptive Server and executing the command on HR_INSTANCE:
sp_cacheconfig 'tempdb_cache' go
Cache Name Status Type Config Value Run Value ------------ ------ ------------ ------------ ------------ tempdb_cache Active Global,Mixed 100.00 Mb 100.00 Mb ------------ ------------ Total 100.00 Mb 100.00 Mb ========================================================================== Cache: tempdb_cache, Status: Active, Type: Global,Mixed Config Size: 100.00 Mb, Run Size: 100.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 20480 Kb 0.00 Mb 100.00 Mb 10 (return status = 0)
Deleting a named cache To delete a named cache completely,
reset its size to 0:
sp_cacheconfig 'tempdb_cache','0'
You cannot delete a named cache if objects are bound to it, and Adaptive Server issues an error message.
If the named cache has multiple configurations, the entry corresponding to the cache in the configuration file is deleted, as are the entries corresponding to the cache in sysconfigures. The cache is deleted the next time the instance is restarted. If the cache has a single configuration, either global or local, cache entry is not deleted from either the configuration file or from sysconfigures. This entry is deleted by either restarting the cluster or by creating a new configuration for the named cache.
When you delete instance-specific configuration, a named cache reverts to its global configuration, if such a configuration exists. Executing sp_cacheconfig on instance SALES_INSTANCE displays this output:
sp_cacheconfig 'tempdb_cache', '0', 'instance SALES_INSTANCE' go
Cache Name Status Type Config Value Run Value ------------ ------ ------------ ------------ ------------ tempdb_cache Active Global,Mixed 100.00 Mb 100.00 Mb ------------ ------------ Total 100.00 Mb 100.00 Mb ========================================================================== Cache: tempdb_cache, Status: Active, Type: Global,Mixed Config Size: 100.00 Mb, Run Size: 100.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 20480 Kb 0.00 Mb 100.00 Mb 10 (return status = 0)
Changing the cache type To reserve a cache for use only by the transaction log, change the cache type to logonly. This change is dynamic. To create a logonly cache at HR_INSTANCE, enter the following. Executing sp_cacheconfig 'log_hr' on instance HR_INSTANCE displays this output:
sp_cacheconfig 'log_hr','logonly','instance HR_INSTANCE'
Cache Name Status Type Config Value Run Value ------------------ ------ -------- ------------ ------------ HR_INSTANCE:log_hr Active Log Only 150.00 Mb 150.00 Mb ------------ ------------ Total 150.00 Mb 150.00 Mb ========================================================================== Cache: HR_INSTANCE:log_hr, Status: Active, Type: Log Only Config Size: 150.00 Mb, Run Size: 150.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 30720 Kb 0.00 Mb 150.00 Mb 10 (return status = 0)
Configuring cache replacement policy If a cache is dedicated to a table or an index, and the cache has little or no buffer replacement when the system reaches a stable state, you can set the relaxed LRU (least recently used) replacement policy. The relaxed LRU replacement policy can improve performance for caches where there is little or no buffer replacement occurring, and for most log caches. To set relaxed replacement policy:
sp_cacheconfig 'log_sales','relaxed','instance SALES_INSTANCE' go
Cache Name Status Type Config Value Run Value ------------------------ ------ ----- ------------ ------------ SALES_INSTANCE:log_sales Active Mixed 100.00 Mb 100.00 Mb ------------ ------------ Total 100.00 Mb 100.00 Mb ========================================================================== Cache: SALES_INSTANCE:log_sales, Status: Active, Type: Mixed Config Size: 100.00 Mb, Run Size: 100.00 Mb Config Replacement: relaxed LRU, Run Replacement: strict LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- ------------- ------------ ------------ ----------- 2 Kb 20480 Kb 0.00 Mb 100.00 Mb 10 (return status = 0)
Setting the cache replacement policy is not dynamic
and requires you to restart Adaptive Server.
