SQL SERVER – Queries Waiting for Memory Allocation to Execute

In one of the recent projects, I was asked to create a report of queries that are waiting for memory allocation. The reason was that we were doubtful regarding whether the memory was sufficient for the application. The following query can be useful in similar case. Queries that do not have to wait on a memory grant will not appear in the resultset of following query.

SELECT TEXT, query_plan, requested_memory_kb,
granted_memory_kb,used_memory_kb, wait_order
FROM sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(MG.plan_handle)

Please note that wait_order will give order of query waiting on memory to execute. This is a very important script, I suggest that you keep it in the permanent list of queries. If ever you notice that your queries are running slow and think that memory is the culprit, do run this query. If there are lots of rows in the result, please try to optimize the queries or increase the memory capacity.

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