sp_poolconfig

Description

Creates, drops, resizes, and provides information about memory pools within data caches.

Syntax

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 "

Parameters

cache_name

is the name of an existing data cache.

mem_size

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.

config_pool

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.

affected_pool

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.

instance_name

in cluster environments – is the name of the instance whose buffer pool you are adjusting.

wash=size

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. Valid values are 0–100. Setting the prefetch limit to 0 disables asynchronous prefetching in a pool.

Examples

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

Creates 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"

Example 7

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'

Example 8

In cluster environments – displays the buffer pool configuration in the default data cache on instance blade1:

sp_poolconfig 'default data cache', 'instance blade1'

Example 9

In cluster environments – displays the buffer pool configuration for named cache c_log on all instances in the cluster:

sp_poolconfig c_log

Usage


Wash percentage


Local asynchronous prefetch percentage

Permissions

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.

Auditing

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

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_cacheconfig, sp_helpcache, sp_logiosize, sp_unbindcache, sp_unbindcache_all