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.

local async prefetch limit=percent

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.


Example 1

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"

Example 2

Moves 16MB of space to the 32K pool from the 64K pool of pub_cache:

sp_poolconfig pub_cache, "16M", "32K", "64K"

Example 3

Reports the current configuration of pub_cache:

sp_poolconfig "pub_cache"

Example 4

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"

Example 5

Changes the wash size of the 2K pool in pubs_cache to 508K:

sp_poolconfig pub_cache, "2K", "wash=508K"

Example 6

Changes the asynchronous prefetch limit for the 2K pool to 15 percent:

sp_poolconfig pub_cache, "2K", "local async prefetch limit=15"


Wash percentage

Local asynchronous prefetch percentage


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.

See also

System procedures sp_cacheconfig, sp_helpcache, sp_logiosize, sp_unbindcache, sp_unbindcache_all