SQL Server, Buffer Manager Object

 

Applies To: SQL Server 2016

The Buffer Manager object provides counters to monitor how SQL Server uses:

  • Memory to store data pages.

  • Counters to monitor the physical I/O as SQL Server reads and writes database pages.

  • Buffer pool extension to extend the buffer cache by using fast non-volatile storage such as solid-state drives (SSD).

Monitoring the memory and the counters used by SQL Server helps you determine:

  • If bottlenecks exist from inadequate physical memory. If it cannot store frequently accessed data in cache, SQL Server must retrieve the data from disk.

  • If query performance can be improved by adding more memory, or by making more memory available to the data cache or SQL Server internal structures.

  • How often SQL Server needs to read data from disk. Compared with other operations, such as memory access, physical I/O consumes a lot of time. Minimizing physical I/O can improve query performance.

This table describes the SQL Server Buffer Manager performance objects.

SQL Server Buffer Manager countersDescription
Background writer pages/secNumber of pages flushed to enforce the recovery interval settings.
Buffer cache hit ratioIndicates the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.
Buffer cache hit ratio baseFor internal use only.
Checkpoint pages/secIndicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
Database pagesIndicates the number of pages in the buffer pool with database content.
Extension allocated pagesTotal number of non-free cache pages in the buffer pool extension file.
Extension free pagesTotal number of free cache pages in the buffer pool extension file.
Extension in use as percentagePercentage of the buffer pool extension paging file occupied by buffer manager pages.
Extension outstanding IO counterI/O queue length for the buffer pool extension file.
Extension page evictions/secNumber of pages evicted from the buffer pool extension file per second.
Extension page reads/secNumber of pages read from the buffer pool extension file per second.
Extension page unreferenced timeAverage seconds a page will stay in the buffer pool extension without references to it.
Extension pages writes/secNumber of pages written to the buffer pool extension file per second.
Free list stalls/secIndicates the number of requests per second that had to wait for a free page.
Integral Controller SlopeThe slope that integral controller for the buffer pool last used, times -10 billion.
Lazy writes/secIndicates the number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.
Page life expectancyIndicates the number of seconds a page will stay in the buffer pool without references.
Page lookups/secIndicates the number of requests per second to find a page in the buffer pool.
Page reads/secIndicates the number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost, either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.
Page writes/secIndicates the number of physical database page writes that are issued per second.
Readahead pages/secIndicates the number of pages read per second in anticipation of use.
Readahead time/secTime (microseconds) spent issuing readahead.
Target pagesIdeal number of pages in the buffer pool.

SQL Server:Buffer Node
Server Memory Server Configuration Options
SQL Server, Plan Cache Object
Monitor Resource Usage (System Monitor)
sys.dm_os_performance_counters (Transact-SQL)
Buffer Pool Extension

Community Additions

ADD
Show: