Creates multiple buffer pools.
sp_poolconfig cache_name [, "mem_size [P\K\M\G]", "config_poolK" [, "affected_poolK"]] [, 'instance instance_name']
cache_name – the name of an existing data cache.
mem_size – 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. Specify size units with P for pages, K for kilobytes, M for megabytes, or G for gigabytes. The default unit is kilobytes.
config_pool – the I/O size performed in the memory pool where the memory is allocated or removed. Valid I/O sizes are multiples of the logical page size, up to eight times the amount of the logical page size.
affected_pool – the size of I/O performed in the memory pool where the memory is deallocated, or the pool’s attributes, such as wash size and prefetch limit, are modified. If affected_pool is not specified, the memory is taken from the lowest logical page size memory pool.
Creating a 4K pool for named cache Executing sp_poolconfig 'tempdb_cache' on instance SALES_INSTANCE displays:
sp_poolconfig 'tempdb_cache','25M','4K' go
Cache Name Status Type Config Value Run Value ------------ ------ ------------ ------------ ------------ tempdb_cache Active Global,Mixed 100.00 Mb 100.00 Mb (1 row affected) ------------ ------------ Total 100.00 Mb 100.00 Mb ========================================================================== Cache: tempdb_cache, Status: Active, Type: Global,Mixed Config Size: 100.00 Mb, Run Size: 100.00 Mb Config Replacement: strict LRU, Run Replacement: strict LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- ------------- ------------ ------------ ----------- 2 Kb 15360 Kb 0.00 Mb 75.00 Mb 10 4 Kb 5120 Kb 25.00 Mb 25.00 Mb 10 (return status = 0)
Creating a pool configuration for local caches You can create an 8K pool for named cache 'log_hr'. Executing sp_poolconfig 'tempdb_cache' on instance HR_INSTANCE displays:
sp_poolconfig 'log_hr','50M','8K','instance HR_INSTANCE' go
Cache Name Status Type Config Value Run Value ------------------ ------ -------- ------------ ------------ HR_INSTANCE:log_hr Active Log Only 150.00 Mb 150.00 Mb (1 row affected) ------------ ------------ Total 150.00 Mb 150.00 Mb ========================================================================== Cache: HR_INSTANCE:log_hr, Status: Active, Type: Log Only Config Size: 150.00 Mb, Run Size: 150.00 Mb Config Replacement: strict LRU, Run Replacement: strict LRU Config Partition: 1, Run Partition: 1 IO Size Wash Size Config Size Run Size APF Percent -------- ------------- ------------ ------------ ----------- 2 Kb 20480 Kb 0.00 Mb 100.00 Mb 10 8 Kb 10240 Kb 50.00 Mb 50.00 Mb 10 (return status = 0)
There is no instance-specific configuration of buffer
pools for a global cache. The instance option is used to detect
the local cache configuration for which pool configuration is necessary.
