SQL Buffers Test

This value is the ratio of the total number of cache hits to the total number of cache lookups since the server was started. Because reading from the cache is much less expensive than reading from disk, this ratio should ideally be high. Generally, one can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server.

Because physical I/O is expensive, one may be able to minimize the cost, either by using a larger data cache, intelligent indexes, more efficient queries, or by changing the database design.

Page writes are generally expensive. Reducing page-write activity is important for optimal tuning. One way to do this is to ensure that you do not run out of free buffers in the free buffer pool. If you do, page writes will occur while waiting for an unused cache buffer to flush.

For optimum performance, the total pages must be close to the target pages value.

This measure will not be available for Microsoft SQL Server 2012.

This measure will not be available for Microsoft SQL Server 2012.

This measure will not be available for Microsoft SQL Server 2012.

Lazy writes are buffers written by the 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.

Keeping the number of lazy writes low can enhance performance. A supply of buffers available for immediate use keeps the number of lazy writes low. Before a requested page can be brought into memory, a free buffer must be available in the buffer pool. If no free buffers are available, an existing buffer must be reused. When an existing buffer has to be reused, many buffer pages might have to be searched in order to locate a buffer to reclaim for use. If the buffer found is marked as dirty or modified, the buffer manager must first write the changes to disk before the page can be reused and assigned to the requesting process. This results in a wait for the requesting process. Waiting processes can degrade performance.

A high value of this measure indicates that your page stays longer in the buffer pool (area of the memory cache), thereby leading to higher performance. This is because, every time a request comes in, the probability of that request been served by the cache is higher; this  in turn significantly reduces direct disk accesses, which are relatively more expensive operations.

A value too low for your system indicates that pages are being flushed from the buffer pool too quickly.

The target on an OLTP system should be at least 300 seconds (5min). When under 300 seconds, this may indicate poor index design (leading to increased disk I/O and less effective use of memory) or, simply, a potential shortage of memory.

Free list stall rates of 3 or 4 per second indicate too little SQL memory available.