sp_poolconfig

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 – (Cluster Edition) is the name of the instance with the buffer pool you are adjusting.
wash=size – Changes the wash size (the point in the cache at which the SAP ASE 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 – (Cluster environment) 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 – (Cluster environment) Displays the buffer pool configuration in the default data cache on instance blade1:
```
sp_poolconfig 'default data cache', 'instance blade1'
```
Example 9 – (Cluster environment) Displays the buffer pool configuration for named cache c_log on all instances in the cluster:
```
sp_poolconfig c_log
```

Usage

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, the SAP ASE 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.
The following figure 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
  
  Data Cache with Default and User-Defined Caches
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 is also deleted or be changed to the desired size when the server is restarted.
You can create memory pools while the SAP ASE server is active; no restart is needed for them to take effect. However, the SAP ASE 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, the SAP ASE 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 the SAP ASE 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 SAP ASE servers perform best with I/O configured for transactions logs that is twice the logical page size. The SAP ASE 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, the SAP ASE server uses the logical page size memory pools.

Permissions

The permission checks for sp_poolconfig differ based on your granular permissions settings.

Setting	Description
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.
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.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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