SQL Server Diagnostic Information Queries Detailed, Day 9

For Day 9 of this series, we start out with Query #19, which is BPE Configuration.  This query retrieves information about your buffer pool extension (BPE) configuration from the sys.dm_os_buffer_pool_extension_configuration dynamic management view. Query #19 is shown in Figure 1.

   1: -- See if buffer pool extensions (BPE) is enabled (Query 19) (BPE Configuration)

   2: SELECT [path], state_description, current_size_in_kb, 

   3: CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]

   4: FROM sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK) OPTION (RECOMPILE);

   5:  

   6: -- BPE is available in both Standard Edition and Enterprise Edition

   7: -- It is a more interesting feature for Standard Edition

   8:  

   9: -- Buffer Pool Extension to SSDs in SQL Server 2014

  10: -- http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx

Figure 1: Query #19 BPE Configuration

BPE was a new feature that was added to SQL Server 2014. The idea behind it is that you can set aside some space in your file system for a read-only cache file of clean buffer pool pages, that makes it look like your buffer pool is larger than it actually is. SQL Server will look in the actual buffer pool first, and then in the BPE file, and finally in your storage subsystem to find the data that it needs. The ideal scenario for this to be helpful is if you have a read-intensive, OLTP workload that does a lot of random reads from your data files (but you don’t have enough RAM to fit the data that is being read into the actual buffer pool). If you are using magnetic storage that has relatively poor random read I/O performance, and if your BPE file is on fast, local flash storage, then, you might see a performance improvement from using BPE.

The problem with this in real life is that there is typically a lot of write activity to the BPE file to keep it up to date with the data that it is trying to cache. Some types of less expensive flash storage has performance issues when it is under sustained write pressure. It is also hard to drive an OLTP workload hard enough to cause it to actually use the BPE file in most scenarios, unless you set max server memory artificially low. The BPE feature is mainly interesting if you are using SQL Server 2014 Standard Edition, where you are restricted to using 128GB of RAM per instance for the Database Engine.

Query #20 is BPE Usage. This query retrieves data from the sys.dm_os_buffer_descriptors dynamic management view. Query #20 is shown in Figure 2.

   1: -- Look at buffer descriptors to see BPE usage by database (Query 20) (BPE Usage) 

   2: SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],

   3: CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)], 

   4: AVG(read_microsec) AS [Avg Read Time (microseconds)]

   5: FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)

   6: WHERE database_id <> 32767

   7: AND is_in_bpool_extension = 1

   8: GROUP BY DB_NAME(database_id) 

   9: ORDER BY [Buffer size(MB)] DESC OPTION (RECOMPILE);

  10:  

  11: -- You will see no results if BPE is not enabled or if there is no BPE usage

Figure 2: Query #20 BPE Usage

This query (which usually takes a few seconds to run), will show you which databases are actually using your BPE file. I have not seen too many people using the BPE feature so far, which I think is a shame. If you have the right kind of workload, it may help performance. It is certainly worth some testing to try it out.