SQL Server, Buffer Manager Object
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.
Buffer Manager Performance Objects
This table describes the SQL Server Buffer Manager performance objects.
| SQL Server Buffer Manager counters | Description |
|---|---|
| Buffer cache hit ratio | Indicates 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. |
| Checkpoint pages/sec | Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed. |
| Database pages | Indicates the number of pages in the buffer pool with database content. |
| Extension allocated pages | Total number of non-free cache pages in the buffer pool extension file. |
| Extension free pages | Total number of free cache pages in the buffer pool extension file. |
| Extension in use as percentage | Percentage of the buffer pool extension paging file occupied by buffer manager pages. |
| Extension outstanding IO counter | I/O queue length for the buffer pool extension file. |
| Extension page evictions/sec | Number of pages evicted from the buffer pool extension file per second. |
| Extension page reads/sec | Number of pages read from the buffer pool extension file per second. |
| Extension page unreferenced time | Average seconds a page will stay in the buffer pool extension without references to it. |
| Extension pages writes/sec | Number of pages written to the buffer pool extension file per second. |
| Free list stalls/sec | Indicates the number of requests per second that had to wait for a free page. |
| Lazy writes/sec | Indicates 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 expectancy | Indicates the number of seconds a page will stay in the buffer pool without references. |
| Page lookups/sec | Indicates the number of requests per second to find a page in the buffer pool. |
| Page reads/sec | Indicates 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/sec | Indicates the number of physical database page writes that are issued per second. |
| Readahead pages/sec | Indicates the number of pages read per second in anticipation of use. |
See Also
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