May 19, 2011 at 12:04 pm
across our suite of DB servers, we typically set SQL min and max levels for memory at 75-80% of total available server memory.
this means the 'available memory' metrics arent very useful, and that we rely on PLE as our main metric indicating memory pressure.
I expect a nice smoothly climbing graph into the 10s of thousands of seconds by the end of each day, and prior to our maintenance period where we do some reindexing, update stats and run sp_recompile is run against all db objects. in some cases this is the case.
in some others though its not. servers with a ton of memory and no indicator of memory issues tend to have choppy PLE graphs over the course of a day, and sometimes hover around 1000-2000 seconds. on some servers with known perf issues, the PLE values are verylow (low hundreds) and readahead pages are high. that seems to be a good sign that memory is taxed and adding memory would help. but what sort of ratios between these two values should we see?
we've recently moved a customer from a 4x4 box with 16gb of memory to a new 6x8 sql 2005 server with 64gb. PLE is still not as high as I'd expect and we do see some fluctuations in readahead pages.
how reliable are these metrics?
based upon conversations ive had with our MS Field engineer, and some training they offered a few months back, these should be our primary indicators of memory pressure, but its just not seeming to wash out the way I'd expect. anyone got any thoughts around this subject?
May 19, 2011 at 12:13 pm
PLE is also a function of the queries that the server runs. It you have a lot of queries that force scans of large tables, then it will remain low until you get to the point where all the needed data is cached.
The solution can be as simple as adding indexes to support these queries or rewriting them to be able to use existing indexes. I see many cases where poorly done queries force a table scan when a few minor changes to the query would have allowed it to use an index.
May 19, 2011 at 12:13 pm
and lazy writes/second as well.
worth mentioning, disk IO metrics and cpu metrics on these servers are well within acceptable limits.
thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply