When a query is executed it needs some memory to run. The wait time for getting that memory is accumulated in the wait time Resource_sempahore.

That can be caused by to little memory, bad execution plans and/or memory intensive queries (e.g. queries that are using hashing and sorting).

CMEMTHREAD can also indicate that queries are awaiting memory.

What to do

First you need to identify the queries.

select t3.text, t4.query_plan, t1.*
from sys.dm_exec_query_memory_grants t1
    inner join sys.dm_exec_requests t2 on t1.session_id=t2.session_id
    cross apply sys.dm_exec_sql_text(t2.sql_handle) t3
    cross apply sys.dm_exec_query_plan(t2.plan_handle) t4   

This query shows the sql statement, executionplan and important memory issues.

Look at the field Grant_time. If that is null, then memory is not granted yet.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.