Buffer Pool Extension

Buffer Pool Extension

•       Extends buffer cache to non-volatile storage

•       Improves performance for read-heavy OLTP workloads

•       SSD devices are often more cost-effective than adding physical memory

•       Simple configuration with no changes to existing applications.

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

    (FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 60 GB);

GO

Returning configuration buffer pool extension information

SELECT path, file_id, state, state_description, current_size_in_kb

FROM sys.dm_os_buffer_pool_extension_configuration;

Returning the number of cached pages in the buffer pool extension file

SELECT COUNT(*) AS cached_pages_count

FROM sys.dm_os_buffer_descriptors

WHERE is_in_bpool_extension <> 0;

Setting process affinity

The examples in this section show how to set process affinity to CPUs and NUMA nodes. The examples assume that the server contains 127 CPUs that are arranged into two groups of 7 NUMA nodes each. Threads are not assigned to any NUMA node or CPU.

• Group 0: NUMA nodes 0 through 3, CPUs 0 to 63
• Group 1: NUMA nodes 4 through 7, CPUs 64 to 127

Setting affinity to all CPUs in groups 0 - The following example sets affinity to all the CPUs in groups 0. 

ALTER SERVER CONFIGURATION 

SET PROCESS AFFINITY CPU=0 TO 63;

Setting affinity to AUTO - The following example sets affinity to AUTO. 

ALTER SERVER CONFIGURATION

SET PROCESS AFFINITY CPU=AUTO;