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_poolK"]]
To change a pool’s wash size:
sp_poolconfig cache_name, "io_size ", "wash=size[P|K|M|G]"
To change a pool’s asynchronous prefetch percentage:
sp_poolconfig cache_name, "io_size ", "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, if a pool already exists with the specified I/O size. The minimum size of a pool is 512K. 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. If affected_pool is not specified, the memory is taken from the logical page size memory pool.
is the size of I/O performed in the memory pool where the wash size is to be reconfigured. The combination of cache name and I/O size uniquely identifies a memory pool.
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.
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"
Moves 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"
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 2K 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 operation.
The combination of cache name and I/O size must be unique. In other words, you can have only one pool of a given I/O size in a particular data cache.
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 8-3 shows a data cache on a server that uses 2K logical pages with:
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-3: Data cache with default and user-defined caches
You can create pools with I/O sizes up to 16K in the default data cache.
The minimum size of a memory pool is 512K. You cannot reduce the size of any memory pool in any cache to less than 512K 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.
The following commands perform only 2K I/O: create database, alter database, some dbcc commands, disk init, and drop table. dbcc checktable can perform large I/O, and dbcc checkdb performs large I/O on tables and 2K I/O on indexes. Also, recovery uses only the 2K memory pool: all pages are read into and changed in the 2K pool of the default cache. Be sure that your default 2K pool is large enough for these activities.
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. 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.
Only a System Administrator can execute sp_poolconfig to reconfigure memory pools within data caches. Any user can use sp_poolconfig to get information about memory pools.
System procedures sp_cacheconfig, sp_helpcache, sp_logiosize, sp_unbindcache, sp_unbindcache_all
