The Buffer Manager object provides counters to monitor how Microsoft® SQL Server™ uses both memory to store data pages, internal data structures, and the procedure cache, and counters to monitor the physical I/O as SQL Server reads database pages from and writes database pages to disk. Monitoring the memory used by SQL Server can help determine, for example, if bottlenecks exist due to a lack of available physical memory for storing frequently accessed data in cache, in which case SQL Server must retrieve the data from disk. By monitoring memory, you can determine 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. Monitoring the physical I/O is especially important in determining how often SQL Server needs to read data from disk. Compared to other operations, such as memory access, physical I/O consumes a lot of time. Minimizing physical I/O can improve query performance.
These are the SQL Server Buffer Manager counters.
| SQL Server Buffer Manager counters | Description |
|---|---|
| Buffer Cache Hit Ratio | 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 since SQL Server was started. 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. |
| Cache Size (pages) | Size of the procedure cache (in pages). Includes all instances of cache sizes monitored for different types of plans under the Cache Manager object in addition to pages currently assigned to the procedure cache but not used by any plan. |
| Checkpoint Writes/sec | Number of pages flushed to disk per second by a checkpoint or other operations that cause all dirty pages to be flushed to disk. |
| Committed Pages | Number of buffer pages committed. |
| ExtendedMem Cache Hit Ratio | Percentage of page requests satisfied from the extended memory cache. |
| ExtendedMem Cache Migrations/sec | Number of pages migrated into the extended memory cache region per second. |
| ExtendedMem Requests/sec | Number of requests for pages from the large memory region per second. |
| Free Buffers | Number of free buffers available. |
| Lazy Writer Buffers/sec | Number of buffers examined by the lazy writer per second. As buffers are needed to read new pages into the cache, the lazy writer buffer pointer is advanced through the cache. Each buffer is examined to determine if they have been touched since the last time they were checked, and for dirty pages that can be written to disk. |
| Lazy Writes/sec | Number of buffers written per second by the buffer manager’s lazy writer. The lazy writer is a system process whose main task is to flush 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 make them available to user processes. The lazy writer eliminates the need to perform checkpoints frequently for the purpose of creating available buffers. |
| Page Reads/sec | 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, more efficient queries, or by changing the database design. |
| Page Requests/sec | Number of requests for buffer pages per second. A page request is a logical read. If the page is found in the cache, there will be no physical I/O associated with the request. |
| Page Writes/sec | Number of database page writes that are issued per second. Page writes are generally expensive. Reducing page-write activity is important for optimal tuning. One way to do this is to ensure that you do not run out of free buffers in the free buffer pool. If you do, page writes will occur while waiting for an unused cache buffer to flush. |
| Readahead Pages/sec | Number of requests per second to asynchronously prefetch pages before they are actually encountered. Provides a general idea of how busy read-ahead is in terms of actual physical I/O. |
| Reserved Page Count | Number of buffer cache reserved pages. |
| Stolen Page Count | Number of buffer cache pages stolen to satisfy other server memory requests. |
| Server Memory Options | SQL Server: Cache Manager Object |
| Pages and Extents |