Buffer Manager

This resource contains performance counters specific to the current status of buffers. The icons of the listed performance counters in the Statistic Name column change color to reflect the listed counter's current state. Clicking any performance counter in the Statistic Name column takes you to the Performance Counter Details page for that counter.

If the value of a listed performance counter crosses the Warning threshold, the chart for the listed counter displays a yellow background. If the value of a listed monitor crosses the Critical threshold, the chart for the listed counter displays a red background. Hovering over any time period within the chart displays detailed information about that time period in the tooltip. Hovering over a counter gives you detailed information about that performance counter.

Counter Expert Knowledge

Total pages

This performance counter returns the number of pages in the buffer pool. The returned value includes database, free, and stolen pages.

This counter is not available in SQL 2012.

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. The higher this ratio, the less often SQL Server has to go to the hard disk to fetch data, and performance is boosted overall. This counter averages the Buffer Cache Hit Ratio from the time the last instance of SQL Server was restarted. This counter is not a real-time measurement, but an average of all the days since SQL Server was last restarted. In OLAP applications, the ratio could be much lower because of the nature of how OLAP works.

Potential Issue: If the returned value is low, this could indicate that your SQL server may not have enough memory to function at peak performance.

Resolution: Check your SQL server and verify its memory is being used efficiently. Applications other than SQL may be using a great deal of memory. Try and recover memory by closing unnecessary applications. Installing additional memory may also help.

Lazy Writes/sec

The lazy writer is a system process that flushes out 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.

This counter tracks how many times per second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. The Lazy Writer eliminates the need to perform frequent checkpoints in order to create available buffers.

This should not be a high value, for example no more than 20 per second. Ideally, the value should be close to zero. If it is zero, this value indicates that your SQL Server's buffer cache is large and your SQL Server does not need to free up dirty pages.

Potential Issue: If the returned value is high, this can indicate that your SQL Server's buffer cache is small and that your SQL Server needs to free up dirty pages.

Resolution: Check your SQL server and verify its memory is being used efficiently. Applications other than SQL may be using a great deal of memory. Try and recover memory by closing unnecessary applications. Installing additional memory may also help.

Free List Stalls/sec

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

This displays the frequency with which requests for available database pages are suspended because no buffers are available.

Potential Issue: If the returned value is high, this indicates that not enough memory is available for the SQL Server.

Resolution: Check your SQL server and verify its memory is being used efficiently. Applications other than SQL may be using a great deal of memory. Try and recover memory by closing unnecessary applications. Installing additional memory may also help.

Zooming

You can have the chart show a predetermined time period of data by clicking on any one of the three Zoom buttons in the head of the Zoom column. Alternatively, you can have the chart show a specific date range by dragging the mouse over an area of the chart. The time and date displayed at the top of the resource shows the amount of data currently loaded into the chart. This can be changed by clicking Edit from within the resource.

The Value from Last Poll column shows the most recent data available for the listed statistic.