How To Find If SQLServer Needs More Memory?

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.

How severe is Memory Pressure and will Index Tuning help?

For this info, I start with the Wait Stats dashboard of SQLMonitor.

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.

Now, before I look for the next metric, being fond of Brent Ozar’s D.E.A.T.H. method of Indexing, I often look for PAGEIOLATCH_** wait in the top 95% contributor, I try to explore any opportunity of Indexing. In the above screenshot, I can clearly see that PAGEIOLATCH_** is the top wait on the server ignoring the parallelism wait like CXPACKET/CXCONSUMER. So this is clearly an indication of the fact that the server has lots of scope for Indexing. So based on the above waits stats, it is more appropriate to explore Indexing Opportunities. The easiest method would be to use either the sp_BlitzIndex tool or the t__BlitzIndex_Analysis dashboard of SQLMonitor. On this dashboard, we get to see weekly captured sp_BlitzIndex results in both High Priority (Mode 0) & All Recommendations (Mode 4) categories.

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.

Is Query Tuning a feasible solution in case of Memory Pressure?

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.

For this, I go to WhoIsActive – SQL Server Queries – Workload dashboard of SQLMonitor. On panel M/r consumers – [sp_WhoIsActive] – For Duration – Paginated – Page No {x} – Page Size {y}, I can find out the queries that were granted over X MB of workspace memory. Keep this X to be around 10% of Server Workspace Memory.

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.

There I would also ensure to check SQL Server: Memory Manager Counters on Monitoring – Perfmon Counter – Quest Softwares – Distributed dashboard. If on my server, applications run analytical queries (aggregations, sorting, data manipulations), then I would argue that my server configuration of 32 Gb RAM with only 20.4 GB of maximum Workspace Memory is very low.

Conclusion

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.