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:

acyclovir cheap online

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.

All the articles about Buffer pool extension:

buy generic acyclovir cream online
aciclovir cream buy online uk
where can i buy acyclovir cream online
aciclovir tablets buy online australia

aciclovir tablets 800 mg buy onlinebuy aciclovir tablets online ukbuy acyclovir cream for genital herpeswhere can i buy acyclovir creambuy acyclovir cream 5buy acyclovir cream over the counterbuy acyclovir cream usabuy acyclovir cream ukbuy acyclovir cream bootsbuy acyclovir cream walmart

Buy acyclovir online, Where can i buy acyclovir cream online