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 run below command to get all performance counters and their respective values using DMV sys.dm_os_performance_counters. 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. I have explained SQL Server Memory Management and how SQL Server allocated memory to their transactions in below attached article.
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. If you want to read CPU and Disk IO related perfmon counters then i would suggest you to read below attached articles.
- Top Perfmon Counters to Identify SQL Server CPU Bottleneck
- Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters
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.
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.
Here, I have described top 10 performance counters to identify SQL Server memory pressure on your database server. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
- Fix Error 833: SQL Server has encountered 32 occurrences of I/O requests taking longer than 15 seconds to complete.
- Disk and Performance Impact of running Online Index Rebuild Operation
- Understanding ASYNC_IO_COMPLETION Wait Type
Latest posts by Manvendra Deo Singh (see all)
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018
- How to Add Data File to Availability Database? - July 11, 2018