• Article

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

Description

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.

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 stalls/sec

Number of requests per second 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 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.

Page lookups/sec

Number of requests per second 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 per second.

Readahead pages/sec

Number of pages read per second 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).