Saturday, September 18, 2010

Sql Server Perfmon counters for Memory

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: