Top 10 Performance Counters to Identify SQL Server Memory Pressure

Top 10 Performance Counters to Identify SQL Server Memory Pressure

Memory is one of the important system resource that SQL Server uses to process their transactions. Sometimes, your server might face memory bottleneck that reduces the performance of running transactions. 

Here, I will explain top 10 performance counters to identify SQL Server memory pressure or memory bottleneck. If your system is facing insufficient system memory then i would recommend you to read below article.

We can use Windows PerfMon utility to gather current values of all given performance counters. Just type perfmon in run prompt to launch perfmon tool to gather memory related performance counters. Although, Microsoft has also given a DMV related to performance counters that is very useful in collecting data and detecting CPU, IO or memory bottleneck in SQL Server. You can also use this DMV to get performance counter values to identify memory pressure in SQL Server.

Select * from sys.dm_os_performance_counters

By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources. 

SQL Server Memory Management and how SQL Server allocated memory to their transactions:

Performance Counters to Identify SQL Server Memory Pressure

Let’s discuss top perfmon counters for memory usages to identify its bottleneck. Below is the list of top 10 performance counters to identify SQL Server Memory pressure. 

Memory: Available Bytes

First thing you should look at in case of memory bottleneck in SQL Server is the available memory that can be used by processes. You can get this value by looking at the counter Available Bytes under object Memory. Low value of this counter shows the memory shortage on this server.  Now, you should focus behind the reason of this memory shortage. There might be possibility that some of your applications running on your server are not releasing memory or your system has not enough memory to perform current workload that leads to the memory bottleneck in SQL Server.

Memory: Pages/sec

This counter indicates the total number of pages that are either read from disk or written to the disk due to page fault. When a process requests a data page that is not in memory then it will be retrieved from disk to memory. This process is known is page fault. High value of this counter could indicate the excessive paging. We should further monitor Memory: Page Faults/sec counter to go indepth information. The average Pages/sec value should be below 50. A high value of Pages/sec indicates intensive memory activities like read from and write to disk. It means transactions are getting enough memory that is why they are using disk to process their requests.

Memory: Page Faults/sec

High value of counter Memory: Pages/sec could indicate the excessive paging. Next, we should monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. If you want to determine whether SQL Server or another process is causing excessive paging then we should monitor the Process: Page Faults/sec counter for the SQL Server process instance. This will give an exact picture whether SQL Server is causing this paging issue or not.

Process: Working Set

This counter shows the amount of memory a process can use. This counter value shows whether you have set optimum value in min server memory and max server memory configuration or not. If this value shows constantly below min server memory and max server memory then you have configured too much memory for your SQL Server.

SQL Server: Buffer Manager: Buffer Cache Hit Ratio

This is very important counter and this counter shows the number of percentage that has been satisfied using data cache. Suppose the value of buffer cache hit ratio for your server is 94 it means 94% of total requests for data have been processed using data cache.

The ideal value for this counter is more than 90. Higher the value better the memory health.

SQL Server: Buffer Manager: Page Life Expectancy

Page Life Expectancy is very important counter to measure memory pressure for your system. This counter indicates the average number of seconds a data page to remain in cache. Ideally, we have read multiple blogs to consider 300 or more as acceptable value but this value varies depending on your system configuration.

The ideal value is more than 300 but always compare this value with your system configuration. Higher the better concept is also applying here.

SQL Server: Buffer Manager: Free List Stalls/sec

This counter also shows a sign of memory pressure or insufficient memory on your server. This displays 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.

SQL Server: Buffer Manager: Lazy Writes/sec

This counter tells about total number of Lazy writer performed in a second.  It means total number of times Lazy Writer process moves dirty pages from the buffer to disk. A lower number is better than a higher number. If value of this counter shows higher value continuously more than 20, then we need to add more memory to the system or we should think about optimizing memory intensive operations.

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 memory pressure or insufficient memory.

SQL Server: Memory Manager: Memory Grants Pending

This counter shows the number of processes waiting for memory grant per second. Any value higher than zero indicates a lack of memory. We can also get this information by using a DMV sys.dm_exec_query_memory_grants. Those processes who are waiting to be granted the required memory will show you waiting with RESOURCE_SEMAPHORE wait type. I have explained about resource semaphore wait type in below attached article.