SQL SERVER内存压力判断

通常情况下都是参考SQLServer:Buffer Manager\Buffer Cache Hit Ratio的数值。

SQLServer:Buffer Manager\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."

但是某些时候SQLServer:Buffer Manager\Buffer Cache Hit Ratio并不能正确反映内存压力，我们必须借助于其它参数来进行判断。

• 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
• Free Pages – Total number of pages on all free lists (free lists track all of the pages in the buffer pool that are not currently allocate to a data page, and are therefore available for usage immediately)
• Page Life Expectancy – Number of seconds a page will stay in the buffer pool without references
• Free List Stalls/sec – Number of requests per second that had to wait for a free page

 

因为SQL SERVER预读机制的存在，通常情况下SQLServer:Buffer Manager\Buffer Cache Hit Ratio只是对存在明显I/O压力或者碎片明显的服务器响应敏感。