Dive into Buffer pool extension

 

The Buffer pool extension feature started from SQL server 2014.

This is a nice addon to SQL server to extend the memory paging to SSD. This is not an enterprise

only  feature, this can be enabled on standard edition.

The database size has grown huge now the Db's having size more than 1 TB is not uncommon and physical memory is still

more expensive than SSD drives.

Let’s dive into the BPE feature.

What is Buffer pool:

SQL Server buffer pool is SQL server  memory that is used for caching table and index data pages as they are modified or read from disk. The SQL buffer pool helps to reduce database file I/O from disk and improve the response time for data retrieval.

 

 

Buffer Pool Extension Buffer pool extension provides the seamless integration of a non-volatile RAM

(that is, solid-state drive). It forces the paging of I/Os between RAM and the SSDs.

This effectively offloads small random I/Os from mechanical disks to SSDs.

Because of the lower latency and better random I/O performance of SSDs,

the buffer pool extension significantly improves I/O throughput.

To check if the Buffer Pool extension is being used. The dmv is sys.dm_os_buffer_pool_extension_configuration

Enable BPE

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON (FILENAME = 'E:\data\BP_Extension.BPE', SIZE = 16 GB);

Note: Change FileName and Size as per your environment.

The Buffer Pool Extension file is created with BPE as file type. The file is being locked by SQL server process.

Let’s insert some test data and create the load on the server to force cache store to use the BPE.

After making some excessive read operation we can validate if the BPE is being used.

To check what is there in BPE

select

db_name(database_id) as [db_name],

is_in_bpool_extension,

cast(count(*) * 100.0 /

(select count(*) from sys.dm_os_buffer_descriptors where database_id = db_id())

as decimal(5, 2)) as [buffer pool, %]

from sys.dm_os_buffer_descriptors

where

database_id = db_id()

group by

database_id, is_in_bpool_extension;

99% of pages cached in the BPE rest are in the memory.

Observations:

 

Nice feature for:

Read Heavy workload

Offload small random IOs from mechanical disk to SSDs. 

Significantly improves IO performance. 

Increased Random IO throughput 

Reduced IO latency  Increased Transaction throughput

 

Limitation:

Not suited for data warehouse workload

Not best fit for Write Heavy workloads

CPU load increases after enabling the BPE