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)

,
Previous Post
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2
Next Post
SQL SERVER – Download Logical Query Processing Poster

Related Posts

5 Comments. Leave new

  • Hi Pinal,

    Wonderful tips. It could be helpful to me right away….

    Reply
  • Very Useful Script!!!

    Reply
  • Hi Pinal,

    Can you please specify the same script for SQL2005

    Reply
  • i think its for sql 2005 itself as its picking data from the DMV’s, as there are not in sql 2000. Dave please correct me if i am wrong.

    Regards,

    Sanjeev Kumar

    Reply
  • Good day! I know this is kinda off topic however I’d figured I’d ask.
    Would you be interested in trading links or
    maybe guest authoring a blog post or vice-versa? My site covers a lot of the same subjects as yours and
    I feel we could greatly benefit from each other.
    If you’re interested feel free to send me an e-mail.
    I look forward to hearing from you! Wonderful blog by the way!

    Reply

Leave a Reply

Menu