Here there are few sql server perfmon counters which will tell you about memory bottleneck for your sql server instance.Monitor these counters and compare your value with below given values.
1- Buffer cache hit ratio--
-Indicates how often sql server can get data from the buffer rather than disk.
-Buffer cache hit ratio > 90% for OLAP
-Buffer cache hit ratio > 95% for OLTP
2-Free list stalls/sec--
-The frequeny that requests for database buffer pages are suspended because there's no buffer available.
-Free list stalls/sec < 2
-if value is high that means memory shoud be increase.
3-Free pages--
-The total no of 8k data pages on all free lists
-Free pages > 640
4-Lazy writes/sec--
-The no of times per second that lazy writer moves dirty pages from buffer to disk to free buffer space.
-Lazy writes/sec < 20
-greater value will indicate memory bottleneck.
5-Page Life Expectancy--
-No of seconds a data page stays in the buffer.
-Page Life Expectancy > 300,otherwise memory pressure is at play.
6-Page Lookups/Sec--
-No of requests to find a page in the buffer.
-Page lookups/sec)/(Batch request/sec)<100
7-Page Reads/sec and Page writes/sec--
-no of physical db page reads and writes issued,respectly
-Value should be < 90
No comments:
Post a Comment