Creates, drops, resizes, and provides information about memory pools within data caches.
To create a memory pool in an existing cache, or to change pool size:
sp_poolconfig cache_name[, "mem_size [P | K | M | G]", "config_poolK" [, "affected_pool K"], instance instance_name]
To change a pool’s wash size:
sp_poolconfig cache_name, "affected_poolK", "wash=size[P|K|M|G]"
To change a pool’s asynchronous prefetch percentage:
sp_poolconfig cache_name, "affected_poolK", "local async prefetch limit=percent "
is the name of an existing data cache.
is the size of the memory pool to be created or the new total size for an existing pool with the specified I/O size. The minimum size of a pool is 256 logical server pages. For a 2K logical page size server, the minimum size is 256K. Specify size units with P for pages, K for kilobytes, M for megabytes, or G for gigabytes. The default is kilobytes.
is the I/O size performed in the memory pool where the memory is to be allocated or removed.
Valid I/O sizes are multiples of the logical page size, up to four times the amount.
is the size of I/O performed in the memory pool where the memory is to be deallocated, or the pools attributes such as 'wash size' and 'prefetch limit' are to be modified. If affected_pool is not specified, the memory is taken from the lowest logical page size memory pool.
in cluster environments – is the name of the instance whose buffer pool you are adjusting.
Changes the wash size (the point in the cache at which Adaptive Server writes dirty pages to disk) for a memory pool.
sets the percentage of buffers in the pool that can be used to hold buffers that have been read into cache by asynchronous prefetch, but that have not yet been used. Valid values are 0–100. Setting the prefetch limit to 0 disables asynchronous prefetching in a pool.
Creates a 16K pool in the data cache pub_cache with 10MB of space. All space is taken from the default 2K memory pool:
sp_poolconfig pub_cache, "10M", "16K"
Creates 16MB of space to the 32K pool from the 64K pool of pub_cache:
sp_poolconfig pub_cache, "16M", "32K", "64K"
Reports the current configuration of pub_cache:
sp_poolconfig "pub_cache"
Removes the 16K memory pool from pub_cache, placing all of the memory assigned to it in the 2K pool:
sp_poolconfig pub_cache, "0K", "16K"
Changes the wash size of the 2K pool in pubs_cache to 508K:
sp_poolconfig pub_cache, "2K", "wash=508K"
Changes the asynchronous prefetch limit for the 2K pool to 15 percent:
sp_poolconfig pub_cache, "2K", "local async prefetch limit=15"
In cluster environments – Creates a a 16KB buffer pool of size 25MB in the default data cache on instance blade1:
sp_poolconfig 'default data cache', '25M', '16K', 'instance blade1'
In cluster environments – displays the buffer pool configuration in the default data cache on instance blade1:
sp_poolconfig 'default data cache', 'instance blade1'
In cluster environments – displays the buffer pool configuration for named cache c_log on all instances in the cluster:
sp_poolconfig c_log
When you create a data cache with sp_cacheconfig, all space is allocated to the logical page size memory pool. sp_poolconfig divides the data cache into additional pools with larger I/O sizes.
If no large I/O memory pools exist in a cache, Adaptive Server performs I/O in logical page size units, the size of a data page, for all of the objects bound to the cache. You can often enhance performance by configuring pools that perform large I/O. A 16K memory pool reads and writes eight data pages in a single I/O for a 2K logical page size server.
The combination of cache name and I/O size must be unique. In other words, you can specify only one pool of a given I/O size in a particular data cache in sp_poolconfig commands.
Only one sp_poolconfig command can be active on a single cache at one time. If a second sp_poolconfig command is issued before the first one completes, it sleeps until the first command completes.
Figure 1-3 shows a data cache on a server that uses 2K logical pages with:
You can create pools with I/O sizes up to 16K in the default data cache for a 2K page size server.
The minimum size of a memory pool is 256 logical pages (for example, a 2K logical page size server, the minimum size is 512K). You cannot reduce the size of any memory pool in any cache to less than 256 pages by transferring memory to another pool.
Two circumstances can create pool less than 512K:
If you attempt to delete a pool by setting its size to zero, and some of the pages are in use, sp_poolconfig reduces the pool size as much as possible, and prints a warning message. The status for the pool is set to “Unavailable/deleted”.
If you attempt to move buffers to create a new pool, and enough buffers cannot be moved to the new pool, sp_poolconfig moves as many buffers as it can, and the cache status is set to “Unavailable/too small.”
In both of these cases, you can retry to command at a later time. The pool will also be deleted or be changed to the desired size when the server is restarted.
You can create memory pools while Adaptive Server is active; no restart is needed for them to take effect. However, Adaptive Server can move only “free” buffers (buffers that are not in use or that do not contain changes that have not been written to disk). When you configure a pool or change its size, Adaptive Server moves as much memory as possible to the pool and prints an informational message showing the requested size and the actual size of the pool. After a restart of Adaptive Server, all pools are created at the configured size.
Some dbcc commands and drop table perform only logical page size I/O. dbcc checkstorage can perform large I/O, and dbcc checkdb performs large I/O on tables and logical page size I/O on indexes.
Most Adaptive Servers perform best with I/O configured for transactions logs that is twice the logical page size. Adaptive Server uses the default I/O size of twice the logical page size if the default cache or a cache with a transaction log bound to it is configured with a memory pool twice the logical page size. Otherwise, it uses the logical page size memory pool.
You can increase the default log I/O size for a database using the sp_logiosize system procedure. However, the I/O size you specify must have memory pools of the same size in the cache bound to the transaction log. If not, Adaptive Server uses the logical page size memory pools.
The default value for the wash size is computed as follows:
If the pool size is less than 300MB, the default wash size is set to 20 percent of the buffers in the pool
If the pool size is greater than 300MB, the default wash size is 20 percent of the number of buffers in 300MB
The minimum setting for the wash size is 10 buffers, and the maximum setting is 80 percent of the size of the pool.
Each memory pool contains a wash area at the least recently used (LRU) end of the chain of buffers in that pool. Once dirty pages (pages that have been changed while in cache) move into the wash area, Adaptive Server initiates asynchronous writes on these pages. The wash area must be large enough so that pages can be written to disk before they reach the LRU end of the pool. Performance suffers when Adaptive Server needs to wait for clean buffers.
The default percentage, placing 20 percent of the buffers in the wash area, is sufficient for most applications. If you are using an extremely large memory pool, and your applications have a very high data modification rate, you may want to increase the size to 1 or 2 percent of the pool. Run sp_sysmon to look for recommendations, or contact Sybase Technical Support for more information about choosing an effective wash size.
The default value for a pool’s asynchronous prefetch percentage is set by the configuration parameter global async prefetch limit. The pool limit always overrides the global limit.
To disable prefetch in a pool (if the global limit is a nonzero number), set the pool’s limit to 0.
See the Performance and Tuning Guide for information on the performance impact of changes to the asynchronous prefetch limit.
The permission checks for sp_poolconfig differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage data cache privilege to reconfigure memory pools. Any user can execute sp_poolconfig to retrieve information about memory pools. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sa_role to reconfigure memory pools. Any user can execute sp_poolconfig to retrieve information about memory pools. |
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 |
|
System procedures sp_cacheconfig, sp_helpcache, sp_logiosize, sp_unbindcache, sp_unbindcache_all
