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.

SQL SERVER - Queries Waiting for Memory Grant - Performance Tuning QueriesWaiting0-800x443

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.

SQL SERVER - Queries Waiting for Memory Grant - Performance Tuning QueriesWaiting

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.

SQL SERVER - Queries Waiting for Memory Grant - Performance Tuning QueriesWaiting1

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)

, ,
Previous Post
SQL SERVER – QDS_LOADDB Wait and Asynchronous Load of Query Store
Next Post
SQL SERVER – sys.dm_os_sys_info and Lock Pages in Memory

Related Posts

1 Comment. Leave new

  • Tom Wickerath
    June 18, 2020 1:49 pm

    I’m curious why the requested and ideal memory values dropped significantly after increasing memory? Were those two screen shots taken on the same server?

    Can you give us an idea of what the before and after memory amounts were?

    Reply

Leave a Reply

Menu