Ten Performance Monitor Counters to Analyze SQL Server Memory Pressure

Many experts agree that memory is the most important resource with regard to SQL Server performance. There are hundreds of performance counters, and therefore, it can be complex to figure out just what to monitor. Here are some suggestions in understanding whether memory pressure might be the performance problem with your SQL Server. You can use Performance Monitor to collect this data.  You can also obtain the SQL Server specific counters using the Dynamic Management View (DMV) that is built into SQL Server. The DMV is an excellent and lightweight option for performance monitoring.  You can use this syntax.

Select * from sys.dm_os_performance_counters

Object	Counter	Description	Desired value
SQL Server: Buffer Manager	Page Life Expectancy	This is the average number of seconds SQL Server expects a data page to remain in cache. On an OLTP system, this should be at least 300 (or 5 minutes). If this is less than 300, it could indicate poor index design or a shortage of memory.	>300
SQL Server: Buffer Manager	Free List Stalls / sec	This monitors the number of requests per second where data requests stall because there are no buffers available. Any value greater than two indicates that SQL Server needs more memory.	<2
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. A lower number is better than a higher number. Zero is ideal. If this number is greater than 20, more memory is needed.	<20
SQL Server: Buffer Manager	Page Reads / sec	Number of physical database page reads that are issued per second. In a normal OLTP system, workloads can support 80 – 90 per second. If the value is higher than 90, it could indicate poor index design or a shortage of memory.	<90
SQL Server: Buffer Manager	Page Writes/sec	Number of database pages physically written to disk per second. In a normal OLTP system, workloads can support 80 – 90 per second. If the values are higher than this, check “lazy writes / sec” and “checkpoint” counters. If all three values are high, this could indicate insufficient memory.	<90
SQL Server: Memory Manager	Memory Grants Pending	This is the number of processes per second that are waiting for a workspace memory grant. Any value higher than zero indicates a lack of memory.	0 on average
SQL Server: Buffer Manager	Checkpoint Pages / sec	This counter monitors the number of dirty pages per second, which are flushed to disk when SQL Server invokes the checkpoint process. High values for this counter may indicate insufficient memory or that the recovery interval (set using sp_configure) is too high.	Defined in Description
Process	Private Bytes	This is the size, in bytes, of memory that this process has allocated that cannot be shared with any other process.	Defined in description
SQL Server: Memory Manager	Target Server Memory(KB)	This is the maximum physical memory SQL Server can consume on the box.	This should be close to the physical size of memory.
SQL Server: Memory Manager	Total Server Memory (KB)	This is the amount of physical memory that is currently assigned to SQL Server.	This should be close to the Target Server Memory (KB).