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 (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – Queries Waiting for Memory Allocation to Execute

  1. 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

  2. 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!

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s