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.
specifies the type of cache.
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.
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"
Reports the current configuration of pub_cache and any memory pools in the cache:
sp_cacheconfig pub_cache
Drops pub_cache at the next start of Adaptive Server:
sp_cacheconfig pub_cache, "0"
Creates pub_log_cache and sets its type to logonly in a single step:
sp_cacheconfig pub_log_cache, "2000K", logonly
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
Creates a cache and sets the size, type, replacement policy and number of cache partitions:
sp_cacheconfig 'newcache', '50M', mixed, strict, "cache_partition=2"
Creating data caches divides Adaptive Server’s single default data cache into smaller caches. You can then configure pools within a data cache to allow Adaptive Server to perform large I/O using sp_poolconfig. You can bind tables, indexes, databases, and text or image chains to a specific cache using sp_bindcache.
The minimum cache size is 256 times the logical page size. For example, a 4K server would have a minimum cache size of 1024K.
When you first create a data cache:
All space is allocated to the logical page size memory pool.
The default type is mixed.
Figure 8-1 shows a data cache for a 2K server with two user-defined data caches configured and the following pools:
The default data cache with a 2K pool and a 16K pool
A user cache with a 2K pool and a 16K pool
A log cache with a 2K pool and a 4K pool
Figure 8-1: Data cache with default and user-defined caches
Creating, dropping, and changing the replacement policy or number of partitions require a restart of Adaptive Server for the configuration to take effect. You cannot configure pools or bind objects to caches until the cache is active, that is, until the server has been restarted.
Other changes to data caches take effect without a restart, including changing the type, creating, dropping, and resizing memory pools with sp_poolconfig, changing the wash percentage of the pools, and binding and unbinding objects.
The default data cache must always have the type default, and no other cache can have the type default.
The Adaptive Server housekeeper task does not do any buffer washing in caches with a type of logonly or in caches with a relaxed LRU replacement policy.
The following commands perform only 2K I/O: disk init, some dbcc commands, and drop table. The dbcc checkdb and dbcc checktable commands can perform large I/O for tables, but perform 2K I/O on indexes. Table 8-7 shows cache usage, depending on the binding of the database or object.
Command |
Database bound |
Table or index is bound |
Database or object not bound |
|---|---|---|---|
create index |
Bound cache |
N/A |
Default data cache |
disk init |
N/A |
N/A |
Default data cache |
dbcc checkdb |
Bound cache |
N/A |
Default data cache |
dbcc checktable, indexalloc, tablealloc |
Bound cache |
Bound cache |
Default data cache |
drop table |
Bound cache |
Bound cache |
Default data cache |
Recovery uses only the logical page size pool of the default data cache. All pages for all transactions that must be rolled back or rolled forward are read into and changed in this pool. Be sure that your default logical page size pool is large enough for these transactions.
When you use sp_cacheconfig with no parameters, it reports information about all of the caches on the server. If you specify only a cache name, it reports information about only the specified cache. If you use a fragment of a cache name, it reports information for all names matching “%fragment%”.
All reports include a block of information that reports information about caches, and a separate block of data for each cache that provides information about the pools within the cache.
The output below, from a server using 2K, shows the configuration for:
The default data cache with two pools: a 2K pool and a 16K pool. The default data cache has 2 partitions.
pubs_cache with two pools: 2K and 16K
pubs_log, with the type set to logonly and cache replacement policy set to relaxed, with a 2K pool and a 4K pool
Cache Name Status Type Config Value Run Value ----------------------- --------- -------- ------------ -------- default data cache Active Default 0.00 Mb 26.09 Mb pubs_cache Active Mixed 10.00 Mb 10.00 Mb pubs_log Active Log Only 2.40 Mb 2.40 M ------------- -------- Total 12.40 Mb 38.49 Mb =================================================================== Cache: default data cache, Status: Active, Type: Default Config Size: 0.00 Mb, Run Size: 26.09 Mb Config Replacement: strict LRU, Run Replacement: strict LRU Config Partition: 2, Run Partition: 2 IO Size Wash Size Config Size Run Size APF Percent -------- --------- ------------ ------------ ----------- 2 Kb 3704 Kb 0.00 Mb 18.09 Mb 10 16 Kb 1632 Kb 8.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 -------- --------- ------------ ------------ ----------- 2 Kb 1228 Kb 0.00 Mb 6.00 Mb 10 16 Kb 816 Kb 4.00 Mb 4.00 Mb 10 =================================================================== Cache: pubs_log, Status: Active, Type: Log Only Config Size: 2.40 Mb, Run Size: 2.40 Mb Config Replacement: relaxed LRU, Run Replacement: relaxed LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- --------- ------------ ------------ ----------- 2 Kb 206 Kb 0.00 Mb 1.01 Mb 10 16 Kb 272 Kb 1.40 Mb 1.39 Mb 10
Table 8-8 lists the meaning of the columns in the output:
Column |
Meaning |
|---|---|
Cache Name |
The name of the cache. |
Status |
One of the following:
|
Type |
“Mixed” or “Log Only” for user-defined caches, “Default” for the default data cache. |
I/O Size |
The size of I/O for a memory pool. This column is blank on the line that shows that cache configuration. |
Wash Size |
The size of the wash area for the pool. As pages enter the wash area of the cache, they are written to disk. This column is blank on the line that shows the cache configuration. |
Config Value or Config Size |
The size that the cache or pool will have after the next time Adaptive Server is restarted. These are the values that take effect the next time Adaptive Server is restarted. If the value is 0, the size has not been explicitly configured, and a default value will be used. |
Run Value or Run Size |
The size of the cache or pool now in use on Adaptive Server. |
Config/ Run Replacement |
The cache policy (strict or relaxed) that will be used for the cache after the next restart, and the current replacement policy. These will be different only if the policy has been changed since the last reboot. |
Config/Run Partition |
The number of cache partitions that will be used for the cache after the next restart, and the current number of partitions. These will be different if sp_cacheconfig has been used to change the number of partitions since the last reboot. |
APF Percent |
The percentage of buffers in the pool that can hold buffers that have been fetched by asynchronous prefetch, but have not been used. |
Total |
The total size of data cache, if the report covers all caches, or the current size of the particular cache, if you specify a cache name. |
The status “Pend” is short for pending. It always occurs in combination with either “Act” for Active or “Del” for Delete. It indicates that a configuration action has taken place, but that the server must be restarted in order for the changes to take effect.
When you first create a new cache, but have not yet restarted Adaptive Server, the status is “Pend/Act”, meaning that the cache has just been configured and will be active after a restart. If you set the size of a cache to 0 to delete it, the status changes from “Active” to “Pend/Del”, meaning that the cache still exists, and still functions, but that it will be deleted at the next restart.
Figure 8-2: Effects of restarts and sp_cacheconfig on cache status
You can also configure caches and pools by editing the configuration file. For more information, see the System Administration Guide.
When Adaptive Server is first installed, all data cache memory is assigned to the logical page size pool of the cache named default data cache. The default data cache is used by all objects that are not explicitly bound to a data cache with sp_bindcache or whose databases are not bound to a cache.
When you create data caches, the memory allocation comes from the default data cache. Memory for caches is allocated out of the memory allocated to Adaptive Server with the total logical_memory configuration parameter. To increase the amount of space available for caches, increase total logical memory, or decrease other configuration settings that use memory. If you need to decrease the size of total logical memory, the space must be available in the default data cache.
You cannot reduce the size of the default data cache to less than one fourth a logical page. In most cases, the default cache should be much larger than the minimum. This cache is used for all objects, including system tables, that are not bound to another cache, and is the only cache used during recovery. For more information, see the System Administration Guide.
A data cache requires a small percentage of overhead for structures that manage the cache. All cache overhead is taken from the default data cache. To see the amount of overhead required for a specific size of cache, use sp_helpcache, giving the size:
sp_helpcache "200M"
10.38Mb of overhead memory will be needed to manage a cache of size 200M
To change the size of an existing cache, specify the cache’s name and the new size.
If you increase the size of an existing cache, all of the added space is placed in the logical page size pool.
To reduce the size of an existing cache, all of the space must be available in the logical page size pool. You may need to use sp_poolconfig to move space from other pools to this pool.
If you have a database or any nonlog objects bound to a cache, you cannot change its type to logonly.
Cache partitions can be used to reduce cache spinlock contention without needing to create separate caches and bind database objects to them. For more information on monitoring cache spinlock contention, see the Performance and Tuning Guide.
You can set the default number of cache partitions for all caches with the configuration parameter global cache partition number. See the System Administration Guide.
To drop or delete a data cache, change its size to 0, as shown in example 3. When you set a cache’s size to 0, the cache is marked for deletion, but it is not dropped until the next restart of the server. The cache remains active, and all objects that are bound to that cache continue to use it.
You cannot drop the default data cache.
If you drop a cache that has objects bound to it, all of the object bindings for the cache are marked invalid the next time you restart Adaptive Server. A message is printed to the error log on restart, giving the database ID, object ID and index ID:
00:95/11/05 18:20:39.42 server Cache binding for database ’6’, object ’8’, index ’0’ is being marked invalid in Sysattributes.
If you subsequently create a cache of the same name, bindings are marked valid when the cache is activated.
You cannot run sp_cacheconfig within a transaction.
Only a System Administrator can execute sp_cacheconfig to change cache configurations. Any user can execute sp_cacheconfig to view cache configurations.
System procedures sp_bindcache, sp_helpcache, sp_poolconfig, sp_unbindcache, sp_unbindcache_all
