Memory Pressure in SQL Server 2012

I have been working on a SQL Server 2012 database project using Folding@home data pulled from here every hour. As a part of that project I’ve been working on performance tuning my queries to be responsive enough to run from a public web page dynamically and I’ve been digging into memory optimizations and the concept of memory pressure- simply the amount of work the physical RAM in your server is having to do and whether or not it is a bottlenexk in the optimal performance of your application. Here are some quick cliff notes about some memory metrics to watch:

Total Server Memory / Target Server Memory Ratio
This measurement should be at around 100% once the steady state is reached.

Page Life Expectancy
Several authorities on the subject, including SQL Server’s Customer Advisory Team (CAT) [3], indicate that the value for this counter should be at least 300 for OLTP applications. SQL Server’s CAT also indicates that values for this counter should never quickly drop by 50% or more.

Buffer Cache hit ratio
OLTP applications it should equal or exceed 98%

Page reads / sec, Page writes / sec and Lazy writes / sec Measurements
If Lazy writes/sec is consistently experiencing non-zero values with a low PLE and elevated values for Page reads/sec and Page writes/sec the server is experiencing buffer pool contention and you will need to go about troubleshooting this problem further.

Memory Grants Pending
SQL Server Customer Advisory Team recommends that the value of this measurement should always be less or equal to 1.

Clipped from:

Leave a Comment

Your email address will not be published. Required fields are marked *