SQL SERVER – Queries Waiting for Memory Grant – Performance Tuning

Recently while I was working with a client on Comprehensive Database Performance Health Check we realized the database on which we were working was under-provisioned with the memory. When I brought it up to the CTO of the organization he was not sure if my findings were correct or not. He asked me if I can provide it scientifically my findings and he will immediately approve the budget. Let us see in this blog post Queries Waiting for Memory Grant.

Here is the query which we should run to identify queries that are waiting for memory to be available. Once the necessary (requested) memory is available the queries will run normally.

Queries Waiting for Memory Grant

I found the following query from this link: SQL SERVER – List Number Queries Waiting for Memory Grant Pending. Run the query from this link and it will give a result very similar to the following.

When you look at the result above it is very clear that there were many queries that were not able to run because they are all waiting for the memory grant.

Now there are two major ways to fix the issues.

Improve Query Performance

We had done everything that we could do tune the queries, and after many attempts, we were convinced that we can’t do anything more to tune the queries. One should spend considerable time tuning queries, views, and indexes before they consider the option to upgrade the memory for your server.

Increase the Memory for SQL Server

After giving the report and showing our efforts to tune queries, finally, CTO for the organization was convinced that their SQL Server needs more memory and approved the budget for the same.

After increasing the memory we noticed that our queries started to run quickly and efficiently.

While I personally do not like to suggest hardware upgrades to my client when they come to take my help of Comprehensive Database Performance Health Check. However, 1 out of 100 cases, a hardware upgrade is the way to go.

Reference: Pinal Dave (https://blog.sqlauthority.com)