SQL Server: Buffer Manager Object
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 SQL Server reads database pages from and writes database pages to disk.
Monitoring the memory and the counters used by SQL Server helps you determine:
- 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.
- 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 to other operations, such as memory access, physical I/O consumes a lot of time. Minimizing physical I/O can improve query performance.
You can also monitor Microsoft Windows® 2000 Address Windowing Extensions (AWE) activity in SQL Server with the AWE counters. For example, you can make sure that SQL Server has enough memory allocated for AWE to run properly. For more information, see Using AWE Memory on Windows 2000 or awe enabled Option.
These are the SQL Server Buffer Manager counters.
| SQL Server Buffer Manager counters | Description |
|---|---|
| AWE Lookup Maps/sec | Number of times that a database page was requested by the server, found in the buffer pool, and mapped. When it is mapped, it is made a part of the server's virtual address space. |
| AWE Stolen Maps/sec | Number of times that a buffer was taken from the free list and mapped. |
| AWE Unmap Call/Sec | Number of calls to unmap buffers. When a buffer is unmapped, it is excluded from the virtual server address space. One or more buffers may be unmapped on each call. |
| AWE Unmap Pages/Sec | Number of SQL Server buffers that are unmapped. |
| AWE Write Maps/Sec | Number of times that it is necessary to map in a dirty buffer so it can be written to disk. |
| 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 an instance of 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. |
| Checkpoint pages/sec | Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed. |
| Database pages | Number of pages in the buffer pool with database content. |
| Free list stall/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 |
| Page life expectancy | Number of seconds a page will stay in the buffer pool without references. |
| Page lookups/sec | Number of requests to find a page in the buffer pool. |
| 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, and more efficient queries, or by changing the database design. |
| Page Writes/sec | Number of physical database page writes issued. |
| 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). |