SQL Server 2014 Buffer Pool Extension part 3: system monitoring

,

In this article I’d like to tell about monitoring tools available in SQL Server 2014 to evaluate the performance of Buffer Pool Extension.

First of all there is a system view sys.dm_os_buffer_pool_extension_configuration that provides information on the configuration of BPE. It shows whether BPE is enabled or not, the location of the file and its size. Although the specification says that there may be several BPE files it is impossible to open all, at least in CTP1. Maybe later the situation will change.

What is more, the column is_in_bpool_extension is added to the system view sys.dm_os_buffer_descriptors, which allows to get information on separate pages. This means now you can get a list of separate pages across databases with regard to how many pages and which ones are stored in the BPE. For example, here is a query allowing you to see the percentage of database pages stored in memory and in BPE.

 

use [BufferPoolExtension_test];

go

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;

go

The result is 22% of pages cached in memory and the rest are in the BPE:

bpe1

Extended Events are gaining more and more popularity. There are 4 events for tracking and monitoring BPE:

XEvent

Description

sqlserver.buffer_pool_extension_pages_written

Page or contiguous set of pages evicted into the buffer pool extension cache.

sqlserver.buffer_pool_extension_pages_read

Page is read from the buffer pool extension cache.

sqlserver.buffer_pool_extension_pages_evicted

Page is evicted from the buffer pool extension cache.

sqlserver.buffer_pool_eviction_thresholds_recalculated

Lazywriter and/or worker clock has wrapped the BUF array and thresholds are re-calculated.

Besides you can use the following performance counters in Performance Monitor.

  • SQLServer:Buffer Manager\Extension page writes/sec
  • SQLServer:Buffer Manager\Extension page reads/sec
  • SQLServer:Buffer Manager\Extension outstanding IO counter
  • SQLServer:Buffer Manager\Extension page evictions/sec
  • SQLServer:Buffer Manager\Extension allocated pages
  • SQLServer:Buffer Manager\Extension free pages
  • SQLServer:Buffer Manager\Extension in use as percentage
  • SQLServer:Buffer Manager\Extension page unreferenced time

All the articles about Buffer pool extension:

Buffer Pool Extension in SQL Server 2014

Buffer Pool Extension in SQL Server 2014 part 2: benchmark testing

SQL Server 2014 Buffer Pool Extension part 3: system monitoring

Buffer Pool Extension in SQL Server 2014 part 4: benchmark testing for update operations

Rate

Share

Share

Rate