Database Reference
In-Depth Information
are applicable only to the Windows OS and not to SQL Server. However, the impact of not enough virtual memory
will affect SQL Server. These counters are collected in order to understand whether the memory pressures on SQL
Server are internal or external. If they are external memory pressures, you will need to go into the Windows OS to
determine what the problems might be.
Buffer Cache Hit Ratio
The
buffer cache
is the pool of buffer pages into which data pages are read, and it is often the biggest part of the SQL
Server memory pool. This counter value should be as high as possible, especially for OLTP systems that should have
fairly regimented data access, unlike a warehouse or reporting system. It is extremely common to find this counter
value as 99 percent or more for most production servers. A low Buffer cache hit ratio value indicates that few requests
could be served out of the buffer cache, with the rest of the requests being served from disk.
When this happens, either SQL Server is still warming up or the memory requirement of the buffer cache is more
than the maximum memory available for its growth. If the cache hit ratio is consistently low, you might consider
getting more memory for the system or reducing memory requirements through the use of good indexes and other
query tuning mechanism, that is, unless you're dealing with reporting systems with lots of ad hoc queries. It's possible
when working with reporting systems to consistently see the cache hit ratio become extremely low.
This makes the buffer cache hit ratio an interesting number for understanding aspects of system behavior, but it is
not a value that would suggest, by itself, potential performance problems. While this number represents an interesting
behavior within the system, it's not a great measure for precise problems but instead shows a type of behavior.
Page Life Expectancy
Page Life Expectancy indicates how long a page will stay in the buffer pool without being referenced. Generally, a low
number for this counter means that pages are being removed from the buffer, lowering the efficiency of the cache
and indicating the possibility of memory pressure. On reporting systems, as opposed to OLTP systems, this number
may remain at a lower value since more data is accessed from reporting systems. It's also common to see Page Life
Expectancy fall to very low levels during nightly loads. Since this is dependent on the amount of memory you have
available and the types of queries running on your system, there are no hard-and-fast numbers that will satisfy a wide
audience. Therefore, you will need to establish a baseline for your system and monitor it over time.
If you are on a machine with nonuniform memory access (NUMA), you need to know that the standard Page
Life Expectancy counter is an average. To see specific measures, you'll need to use the Buffer Node:Page Life
Expectancy counter.
Checkpoint Pages/Sec
The Checkpoint Pages/sec counter represents the number of pages that are moved to disk by a checkpoint operation.
These numbers should be relatively low, for example, less than 30 per second for most systems. A higher number
means more pages are being marked as dirty in the cache. A
dirty page
is one that is modified while in the buffer.
When it's modified, it's marked as dirty and will get written back to the disk during the next checkpoint. Higher values
on this counter indicate a larger number of writes occurring within the system, possibly indicative of I/O problems.
But, if you are taking advantage of the new indirect checkpoints, which allow you to control when checkpoints occur
in order to reduce recovery intervals, you might see different numbers here. Take that into account when monitoring
databases with the indirect checkpoint configured.
