Total Pageviews

Saturday, November 1, 2014

Buffer Pool Extension (BPE) in SQL 2014 - A brief introduction

What is Buffer Pool (BP)?
The Buffer Pool (also known as the buffer cache) is a shared memory area which consists of data/index pages. A buffer page is 8KB in size and the Max Server Memory determines the total size of the Buffer Pool. For example, when we set max server memory=16GB, it means we are allocating 16GB of memory to the Buffer Pool. The plan cache is also the part of the Buffer Pool.

What is Buffer Pool Extension (BPE)?
This is a new feature in SQL 2014 where we can extend the Buffer Pool beyond the limitation of the maximum physical memory. This means that we can use a persistent disk file system as part of the buffer. The file can be placed in any disk including the network shared folder.

Suitability of BPE:
The primary goal of BPE implementation is to improve Read performance of an OLTP application where physical memory is less than 64GB (according MSDN blogs). The application which is more READ intensive and less WRITE prone, implementing this feature will enhance I/O performance greatly. However, this feature is not suitable for data warehouse and WRITE intensive type workload.

Where to place the file?
The SSD provides very low random and read access latency, thus the best place to put the BPE file in a SSD. If it is placed in a normal disk system, there will be a negative impact on I/O.

BPE size:
The BPE size can be 32 times greater than Max Server’s Memory. So if we set Max Server Memory to 4 GB, then BPE can be 4X32 GB = 128 GB. Microsoft recommends 4 to 8 times of the amount of Max Server Memory which will be an optimal size for overall I/O performance.

What will be written in a BPE file?
The BPE file only holds “clean pages” in it. A “clean page” (aka un-modified page) is a data or index page which is exact and equivalent to a disk page residing in the data file. A BPE file will not hold any “dirty pages” at any time.

Good to know and good practices:
Before taking advantage of the features of BPE, we should be sure that adding extra physical memory is not an option due to hardware limitation. In addition to this, we should have optimal T-SQL code and we should not have I/O overhead index schema such as overlapping, duplicating and multi-column indexes.

1.      Placing the BPE file in a high performance SSD is recommended.
2.      Make sure the disk alignment is correct.
3.      Consider formatting the disk with 64K block (cluster) size.
4.      Set the Max Server Memory and leave enough memory for the OS.
5.      “Lock pages in Memory” has no interaction with BPE.
6.      Before modifying the BPE, the Buffer Pool Extension feature should be disabled first.
7.      When the BPE is disabled, all related configuration settings are removed automatically and the memory used to support the feature is not reclaimed until the instance of SQL Server is restarted. The Buffer Pool Extension file will be deleted once the instance of SQL Server is shutdown.
8.      BPE can be configured and disabled at any time while the instance of SQL Server is running. If the feature is re-enabled, the memory will be reused without restarting the instance.
9.      As BPE contains only clean pages, data loss is out of question.

How to configure BPE:
Configuring BPE is very straight forward and simple. Following are the steps we need to follow to configure a 16GB BPE file when the Max Server Memory is set to 4GB.

Set the “max server memory” to 4GB:
EXEC sp_configure 'max server memory (MB)', 4096
GO
RECONFIGURE

Configure the Buffer Pool Extention to 16GB:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
    (FILENAME = 'E:\SQLBuffer\ExtendedBuffer.BUF', SIZE = 16 GB);

To see the configuration, execute following DMV query:
SELECT * FROM   sys.dm_os_buffer_pool_extension_configuration

To turn off the BPE feature, simply execute the following: 
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF

Querying BPE utilization:
The following query can be used to identify utilization of BPE:
SELECT CASE
            WHEN database_id = 32767 THEN 'ms_resource_db'
            ELSE DB_NAME(database_id)
       END       AS database_name,
       COUNT(*)  AS cached_pages_count,
       CONVERT(NUMERIC(25, 4), COUNT(row_count) * 8.00 / 1024.00) AS size_mb,
       CONVERT(
           NUMERIC(25, 4),
           COUNT(row_count) * 8.00 / 1024.00 / 1024.00
       )         AS size_gb
FROM   sys.dm_os_buffer_descriptors
WHERE  is_in_bpool_extension = 1
GROUP BY
       database_id

    
Performance measurement of BPE:
There are some performance counters that have been introduced in SQL 2014 to measure BPE performance. It can be monitored using Windows PerfMon or with T-SQL. Following is the T-SQL query:

SELECT [object_name], counter_name, instance_name, cntr_value
FROM   sys.dm_os_performance_counters
WHERE  [object_name] LIKE '%Buffer Manager%'
       AND [counter_name] LIKE '%Extension%'


References:
Buffer Pool Extension

Buffer Pool Extension to SSDs in SQL Server 2014