Export (0) Print
Expand All

SQL Server, Buffer Manager Object

The Buffer Manager object provides counters to monitor how SQL Server uses:

  • Memory to store data pages.

  • Counters to monitor the physical I/O as SQL Server reads and writes database pages.

  • Buffer pool extension to extend the buffer cache by using fast non-volatile storage such as solid-state drives (SSD).

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.

This table describes the SQL Server Buffer Manager performance objects.

SQL Server Buffer Manager counters

Description

Buffer cache hit ratio

Indicates the 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 or by using the buffer pool extension feature.

Checkpoint pages/sec

Indicates the number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

Database pages

Indicates the number of pages in the buffer pool with database content.

Extension allocated pages

Total number of non-free cache pages in the buffer pool extension file.

Extension free pages

Total number of free cache pages in the buffer pool extension file.

Extension in use as percentage

Percentage of the buffer pool extension paging file occupied by buffer manager pages.

Extension outstanding IO counter

I/O queue length for the buffer pool extension file.

Extension page evictions/sec

Number of pages evicted from the buffer pool extension file per second.

Extension page reads/sec

Number of pages read from the buffer pool extension file per second.

Extension page unreferenced time

Average seconds a page will stay in the buffer pool extension without references to it.

Extension pages writes/sec

Number of pages written to the buffer pool extension file per second.

Free list stalls/sec

Indicates the number of requests per second that had to wait for a free page.

Lazy writes/sec

Indicates the 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

Indicates the number of seconds a page will stay in the buffer pool without references.

Page lookups/sec

Indicates the number of requests per second to find a page in the buffer pool.

Page reads/sec

Indicates the 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

Indicates the number of physical database page writes that are issued per second.

Readahead pages/sec

Indicates the number of pages read per second in anticipation of use.

Community Additions

ADD
Show:
© 2014 Microsoft