Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

SQL 2014 Learning Series # 4 – New Feature – Buffer Pool Extension

Posted by blakhani on April 8, 2014


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

 

image

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”

image

 

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.

 image


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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    6 Responses to “SQL 2014 Learning Series # 4 – New Feature – Buffer Pool Extension”

    1. manu said

      Thanks for mentioning the “dual write design” concept it clears down the confusion of write mechanism.

    2. kurt said

      one question
      if BPE files can store dirty pages, when the exetension file or the device lost, is it possible to recover these dirty pages from transaction log? And what will happend when a part of memory that store dirty pages is lost, like hot-pluging?

      • blakhani said

        @Kurt – Dirty page is written to both MDF and BPE. So there is no need to recover them.

        • kurt said

          In your post, “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. “. So I want to know why dirty pages can not be stored in BPE, because the dirty pages can not recovered or some other reasons. thanks!

    3. sumit03 said

      Hi,

      We are getting continuous error in our SQL Server 2014 Windows VM Env. We have applied all the necessary patch and our SQL server is up to date.

      Microsoft SQL Server 2014 (SP1-CU3-OD) (KB3097972) – 12.0.4432.0 (X64)
      Copyright (c) Microsoft Corporation
      Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

      “L2 buffer pool write error. BUF address 0xa3f4fcc0. Bufno 2626314240. Extension offset 25001541632. Error code 0. Database file name D:\tempdb2.mdf. Database file offset 2626314240.”
      After that error log is flooded with
      “Time out occurred while waiting for buffer latch — type 4, bp 00000005A3F4FCC0, page 4:320595, stat 0xc0010d, database id: 2, allocation unit id: 71917084154920960, task 0x0000000002DE84E8 : 0, waittime 61500 seconds, flags 0x100000003a, owning task 0x0000000002CEC8C8. Continuing to wait.”

      Server configuration:

      SQL Server 2014 on Windows 2012 R2 Datacenter edition
      Its a D series VM where D (Temporary) drive is SSD .
      BPE and TempDB are residing on D drive.
      Max server Memory = 10 GB
      BPE = 40 GB
      Tempdb Datafiles = 8 each of 8GB initial size with autogrowth = 512 MB
      Log file =1 of 4 GB with 200MB autogrowth set

      Can you please help me out why we are getting so many Alerts??

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: