Help: SQL Server

Today I am going to pick up another new feature of SQL Server 2014 called as Buffer Pool Extension (a.k.a. BPE).  As the name suggests, this feature allows DBA to extend buffer pool beyond the size of the memory allocated to SQL Server. Buffer pool is biggest chunk of memory allocated in SQL Server process. BPE can use nonvolatile storage devices (like SSD) for increasing amount of memory available for buffer pool consumers. This allows usage of SSD as an intermediate buffer pool pages which would help in getting price advantage over the memory increase on the server. Adding storage device on server is less invasive as compare to adding memory and fetching a page from SSD is faster than fetching it from data file. You can think of this as pagefile for SQL buffer pool only.

Steps to configure:

To enable the feature we need provide file path and the size. That’s it? Well, keep in mind that size of BPE should be more than current memory allocation, else we would get below error
Msg 868, Level 16, State 1, Line 40
Buffer pool extension size must be larger than the current memory allocation threshold 12784 MB. Buffer pool extension is not enabled.
Due to above check, I have reduced max server memory on my SQL instance so that I can create the demo.

EXEC sys.sp_configure N'show advanced options', N'1'  
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  
GO
RECONFIGURE WITH OVERRIDE
GO

using above commands, now the max server memory if 1 GB. Then I ran below command to create BPE file of 2 GB (to avoid error 868)

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION 
ON ( FILENAME = 'F:\BufferPoolExtensionDemo\BPE.bpe' ,SIZE = 2 GB) 

To get configuration information

Select * from sys.dm_os_buffer_pool_extension_configuration

Read state_description carefully. It says “BUFFER POOL EXTENSION CLEAN PAGE CACHING ENABLED”. Clean page is a page in SQL memory which has no write pending. It’s generally the page which has been read from data file and no transaction has done the update of that page. Any modification to clean page would make it “dirty page”, which means there is some write on the page which is not yet written to the data file. Only clean pages can be stored in BPE file.

As per whitepaper mentioned in blog “The dual write design is that dirty pages evicted from the buffer pool are written both to the SSD and to the database on disks; in effect, treating the SSD as a “write-through” cache for dirty pages”

Where is my page? memory or BPE file?

To identify that, we can use DMV sys.dm_os_buffer_descriptors which has additional column introduced called is_in_bpool_extension. Here is the query to show such pages.

Select    *
from    sys.dm_os_buffer_descriptors
where    is_in_bpool_extension = 1

What if you want to change the file size of BPE? In case we want to change the configuration, the only option is to disable and enable the configuration. There is no ALTER command to modify it. Here is the command to turn it off.

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF

If we query sys.dm_os_buffer_pool_extension_configuration again, we’ll see new information on our buffer pool extension.

To monitor. Below XEvents are available in SQL Server 2014 to monitor the functionality of BPE

sqlserver.buffer_pool_extension_pages_written
sqlserver.buffer_pool_extension_pages_read
sqlserver.buffer_pool_extension_pages_evicted
sqlserver.buffer_pool_eviction_thresholds_recalculated

Here are various messages which contains text buffer pool extension. (taken from sys.messages catalog view)

Buffer pool extension “%.*ls” has been initialized successfully with size is %I64d MB.
Failed to create buffer pool extension of size %I64d MB on path “%.*ls”.
Buffer pool extension configuration “%.*ls” is in wrong format. The format should be “<extension directory>,<size in GB>”.
Buffer pool extension size must be larger than the physical memory size %I64d MB. Buffer pool extension is not enabled.
Attempt to disable buffer pool extension when in state %ls is not allowed.
Attempt to enable buffer pool extension when in state %ls is not allowed.
Attempting to allocate %1ld BUF for buffer pool extension for a maximum of %2ld page descriptors.
Buffer pool extension is only supported on Standard and Enterprise Editions of SQL Server.
Buffer pool extension is not supported on the %ls architecture.
Buffer pool extension has been disabled successfully. Deleting buffer pool extension “%.*ls”.
Buffer pool extension size must be larger than the current memory allocation threshold %I64d MB. Buffer pool extension is not enabled.
Buffer pool extension “%.*ls” cannot be closed because %ld asynchronous I/Os are outstanding.
Could not change the value of the ‘BPoolExtensionPath’ property. Operating system error %ls

Important Points:

• Since BPE files stores only clean pages, there is no risk of data loss in event of loss of extension file or device which stores the file.
• Available in Enterprise (developer and evaluation) and standard edition feature. Note that this is only for 64 bit SQL Server.

I would not recommend putting BPE on spinning media. Since I don’t have SSD, I have used F drive but that should NOT be done in production.

For more information on the buffer pool extension, please read book online topic Buffer Pool Extension.

Hope this is helpful.