Buffer Manager & Memory Performance Counters

S.NO Performance Counter Name Comments
1 SQL Server:Buffer Manager – Buffer cache hit ratio The percentage of pages that were found in the memory. Higher the value the better. Preferred around the 90% mark. This included the availability of the procedure and the data cache in the system.
2 SQL Server:Buffer Manager – Database pages This number shows the number of pages that constitute the SQL data cache. A large changes in this value indicates the database is swapping cache values from the cache. We need to either increase the memory of the system or the max server memory parameter.
3 SQL Server:Buffer Manager – Procedure cache pages This indicates the number of procedures are present in the cache. This is the location where the compiled queries are stored.
4 SQL Server: Buffer Manager-Free List Stalls/sec Monitors the number of requests per second where data requests stall because no buffers are available. Any value above 2 means SQL Server needs more memory.
5 SQL Server: Buffer Manager-Lazy Writes/Sec Monitors the number of times per second that the Lazy Writer process moves dirty pages from the buffer to disk as it frees up buffer space. Lower is better with zero being ideal. When greater than 20, this counter indicates a need for more memory.
6 SQL Server: Buffer Manager-Checkpoint Pages/sec Monitors the number of dirty pages, per second, that are flushed to disk when SQL Server invokes the checkpoint process. Checkpoint frequency is influenced by the recovery interval setting in sp_configure. High values for this counter may indicate insufficient memory or that the recovery interval is too high.
7 SQL Server: Buffer Manager-Page Life Expectancy Tells, on average, how many seconds SQL Server expects a data page to stay in cache. The target on an OLTP system should be at least 300 (5 min). When under 300, this may indicate poor index design (leading to increased disk I/O and less effective use of memory) or, simply, a potential shortage of memory.
8 SQL Server: Buffer Manager-Page Lookups/sec The number of requests to find a page in the buffer pool. When the ratio of batch requests to page lookups crests 100, you may have inefficient execution plans or too many adhoc queries.
9 SQL Server: Buffer Manager-Page Reads/sec Number of physical database page reads issued per second. Normal OLTP workloads support 80 – 90 per second, but higher values may be a yellow flag for poor indexing or insufficient memory.
10 SQL Server: Buffer Manager-Page Writes/sec Number of database pages physically written to disk per second. Normal OLTP workloads support 80 – 90 per second. Values over 90 should be crossed checked with “lazy writer/sec” and “checkpoint” counters. If the other counters are also high, then it may indicate insufficient memory.
11 SQL Server: Buffer Manager-Readahead/sec Number of data pages read per second in anticipation of their use. If this value is makes up even a sizeable minority of total Page Reads/sec(say, greater than 20% of total page reads), you may have too many physical reads occurring.
12 SQL Server: Buffer Manager-Free System Page Table Entries Shows the number of page table entries (PTE) not in use on the server. PTEs are used to map virtual to physical memory addresses and are affected by the /PAE and /3GB Windows bootswitches.

Leave a Reply

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