Troubleshooting SQL Server with High Memory Grants

Spread the love

Very often, specially on mixed workload enviroment where server deals with oltp & olap queries together, concurrent sessions allocated with High Memory Grants cause entire SQL Server workload to suffer.

To detect such a situation, I like to watch my critical metrics dashboards. For example, dashboard Monitoring – Live -All Servers of free SQLMonitor tool highlights all these metrics.

Monitor SQL Server Health using free open source tool

Once I know my server is having issues from Monitoring – Live -All Servers, I check to see individual & historical metrics for the server on Monitoring – Live – Distributed dashboard. This dashboard is specifically designed for all troubleshooting metrics in a single place for one server.

Troubleshoot SQL Server Memory Related Performance Issues using Free Tools
Troubleshoot SQL Server Memory Related Performance Issues using Free Tools
Troubleshoot SQL Server Memory Related Performance Issues using Free Tools

Once I know that a server is facing memory pressure due to some queries granted with high memory, the next step I take is to find out those sessions, and if allowed kill them. The unseen problem at this point is, even DBA diagnostics queries also might now work if asking for some significant memory grant.

So in this scenario, we need a diagnostic query, which is lightweight in terms of memory footprint. Below is the query I wrote to get the sessions/queries consuming high memory –

Query to find Top Memory Consumers inside SQL Server

From the above result, usually whatever session/db_name/login/program I am interested in debugging, I can get deeper. But at the moment since the server is suffering because of memory pressure, I prefer to kill the top memory consumers after the consent of the Server Owner. This is the point where the below kill query is used –

In above query, @memory_threshold_mb can be used to control the Memory Grant threshold for query. With @debug = 1, we don’t kill the memory consumer sessions, rather just display the session details using sp_WhoIsActive. Below is a sample result with @debug = 1, With @include_open_tran = 1, we kill even the sessions having an open transaction in them if they happen to be memory consumers above the threshold.

Query to kill top memory consumers in SQL Server

Above appropriate parameter values, we can find & kill the top memory consumers using the above script and release the server from memory pressure.

Once the memory pressure is gone, we need to provide RCA. For this purpose, in other circumstances, I fetch captured history from sp_WhoIsActive capturing that we do as part of our free & open source SQLMonitor tool. But when memory pressure is to such a scale where queries get stuck with RESOURCE_SEMAPHORE waits, even WhoIsActive capturing might get stuck because of memory issues.

Free monitoring tool to analyze SQL Server performance using Wait Stats

So at this point, I go back to my next favorite tool sp_BlitzCache. I use the below query –

Sample result on my lab server having memory pressure –

Using sp_BlitzCache to find top memory consuming queries

I hope this will be helpful to DBAs dealing with Memory Pressure on server due to bursty memory hungry workload. If you like the article, do like and share.

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.