SQL Server, Buffer Manager Object
The Buffer Manager object provides counters to monitor how 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 and writes database pages.
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.
You can also monitor 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 Memory Architecture, Using AWE, or awe enabled Option.
This table describes the SQL Server Buffer Manager performance objects.
SQL Server Buffer Manager counters
AWE lookup maps/sec
Number of times per second 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 per second that a buffer was taken from the free list and mapped.
AWE unmap calls/sec
Number of calls to unmap buffers per second. 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 per second.
AWE write maps/sec
Number of times per second 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 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.
Number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.
Number of pages in the buffer pool with database content.
Free list stalls/sec
Number of requests per second that had to wait for a free page.
Total number of pages on all free lists.
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
Number of seconds a page will stay in the buffer pool without references.
Number of requests per second to find a page in the buffer pool.
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.
Number of physical database page writes issued per second.
Number of pages read per second in anticipation of use.
Number of buffer pool reserved pages.
Number of pages used for miscellaneous server purposes (including procedure cache).
Ideal number of pages in the buffer pool.
Number of pages in the buffer pool (includes database, free, and stolen pages).