SQL SERVER – List Number Queries Waiting for Memory Grant Pending

3 years ago

I have been recently writing about Memory Grant Feedback a lot and I have received a lot of questions about it. I have been answering each of them personally but some questions are so good that I feel like answering them on the blog will help more people. Today, in this blog post let us see how we can List Number Queries Waiting for Memory Grant Pending. I had built this simple query for my Comprehensive Database Performance Health Check.

Memory Grant Pending

Here is the query which I run to know how many queries in my system are still waiting for the memory grant.

SELECT @@SERVERNAME AS [Server Name],
cntr_value AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%'
AND counter_name = N'Memory Grants Pending'

If I see the counter value to zero, that is a good thing as there are not queries which are waiting on the memory grant and there is absolutely no issue with the performance of your query.

I usually run this query multiple times to make sure that indeed the value which I am getting via my query is consistent across an interval of time.

If I see very high value in my counter, I usually run the query which is listed on the blog How to List Queries With Memory Grant and Execution Plan? post which lists all the queries which need memory grant and their value. Please note that this query will not display all the running queries, it will only display the queries which need memory grant and display that. I am re-producing the query here for your easy reference.

SELECT mg.session_id
,mg.granted_memory_kb
,mg.requested_memory_kb
,mg.ideal_memory_kb
,mg.request_time
,mg.grant_time
,mg.query_cost
,mg.dop
,st.[TEXT]
,qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY mg.required_memory_kb DESC;

Please note that this query will not display all the running queries, it will only display the queries which need memory grant and display that. Pay special attention to the column grant_time, if the value of this column is NULL, that means the query is still waiting for the memory grant.

Well, that’s it, I hope this blog post helps you to identify if your system is struggling with the memory or not. If there is memory pressure, you can also look at the queries which need help by running the query listed in this blog post.

Here are additional blog posts, I suggest you read to learn more about the new feature of SQL Server, Memory Grant Feedback.

Run the query on your server and do let me know if you find the queries helpful to you or not. If you want me to add more columns to the query, do let me know and I will be happy to look at the request and modify the query to be meaningful to everyone.

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