As a DBA, I often deal with a variety of SQL Server Performance Issues. The most common place I detect SQL Servers having issues is the Monitoring – Live – All Servers dashboard of my open-source monitoring tool SQLMonitor.
Memory Pressure on SQL Server is very common and is presented as Memory Grant Pending metric in the above dashboard screenshot. In my previous blog Troubleshooting SQL Server With High Memory Grants, I discussed what actions should be taken in order to get the server out of constant memory grant issues in real-time.
For long-term resolution, there are various metrics to be validated before making any decision.
As we can see in the above screenshot, for a clock time of 4.99 days, SQL Server has accumulated a whopping total of 3.37 weeks of RESOURCE_SEMAPHORE wait. Also, on average, a request has to wait for 2.58 seconds for a memory grant whenever it is facing RESOURCE_SEMAPHORE wait. Wait PerCore Per Minute of 7.09 seconds is also very very high for this poison wait type. To conclude, yes, the server is heavy facing high memory internal pressure.
Let’s assume the worst-case scenario where I am not allowed for Index Optimization. That means, my server is facing memory pressure throughout the day, and I have to check if I should suggest Query Tuning or more Memory Addition.
The primary requirement for the feasibility of query tuning is the availability of dedicated developers who are ready to take on the challenge of query optimization. If this is satisfied, then the next thing would be to verify if the workload causing memory pressure can be optimized within the acceptable time frame. Very often a complex query can take from a few hours to a few days in order to optimize. So it’s always best to validate what queries/workloads are causing the memory pressure.
Say, for a server of 32 GB RAM with a max memory setting of 85% (26-27 Gb), the SQL Server workspace would be around 19-20 Gb. So choosing M/r Grant (MB) threshold of 2 Gb would be appropriate. Below is a sample result.
In the above result, I try to look for a glimpse of distinct queries that are causing this internal memory pressure on the server by asking for huge memory grants. The same conclusion can be derived more effectively by querying table dbo.WhoIsActive that stores captured data of sp_WhoIsActive in SQLMonitor tool. Utilizing query_plan & query_hash from within query_plan data, we more accurately identify the distinct query list. If this list of large memory-consuming queries is large, then query tuning is probably not a good idea.
If this distinct list of memory consumers over 10% of workspace memory is very small, then there is a high chance that adding more memory would help in server performance.
If the server is facing memory pressure only at specific hours of the day, then probably we can get away with query tuning or index tuning for that specific workload running during those hours.
But if the memory pressure is felt all day long on the server, then depending on the availability of developers & how many distinct memory consumer queries are to be tuned, query tuning can be explored.
If the server has sustained memory pressure throughout the day, and each memory consumer has less than 10% of memory consumption footprint (compared to Workspace Memory), or if the workspace memory is too small for the kind of server workload, then it makes more sense for scaling up the server RAM.
I hope this will be helpful to DBAs dealing with Memory Pressure on their servers. If you like the article, do like and share.
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.