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:
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
