The Buffer Manager object provides counters to monitor how Microsoft SQL Server uses:
Memory to store data pages, internal data structures, and the procedure cache.
Counters to monitor the physical I/O as Microsoft SQL Server reads database pages from, and writes database pages to, the disk.
Default Collection Interval — Every 15 minutes
Table 1-6 Buffer Manager Metrics
| Metric | Description |
|---|---|
|
Buffer Manager Counter Name (key column) |
Performance metric name. See Table 1-7. |
The Buffer Manager Counter Name key column contains several metrics. Table 1-7 provides a list of these metrics and a description for each.
Table 1-7 Buffer Manager Counter Name Metrics
| Metric | 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 Microsoft SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is much less expensive than reading from disk, this ratio should be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to Microsoft SQL Server. |
|
Buffer Cache Hit Ratio Base |
Denominator ("base") of a fraction that the performance counter Buffer Cache Hit Ratio represents. |
|
Checkpoint Pages/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. |
|
Database Pages |
Total number of database pages. |
|
Free List Stalls/sec |
Number of requests that had to wait for a free page. |
|
Free Pages |
Total number of pages on all free lists. |
|
Lazy Writes/sec |
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 make them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers. |
|
Page Lookups/sec |
Number of requests to find a page in the buffer pool. |
|
Page Reads/sec |
Number of physical database page reads 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 by using a larger data cache, intelligent indexes, more efficient queries, or by changing the database design. |
|
Page Writes/sec |
Number of database page writes 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. |
|
Procedure Cache Pages |
Number of pages used to store compiled queries. |
|
Readahead Pages/sec |
Number of pages read in anticipation of use. |
|
Reserved Pages |
Number of buffer pool reserved pages. |
|
Stolen Pages |
Number of pages used for miscellaneous server purposes (including procedure cache). |
|
Target Pages |
Ideal number of pages in the buffer pool. |
|
Total Pages |
Number of pages in the buffer pool (includes database, free, and stolen pages). |