(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
Description:
This wait type is when a thread is waiting for a query execution memory grant so it can begin executing. Memory grants are used for performing query operations like sorts and hashes.
(Books Online description: “Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.”)
Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.
Added in SQL Server version:
Pre-2005/2005
Removed in SQL Server version:
N/A
Extended Events wait_type value:
The map_key value in sys.dm_xe_map_values is 107 in 2008 and 2008 R2, and 111 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.
Other information:
High waits of this type are because queries cannot get the memory they need to execute, and you may also see error 8645 (“A timeout occurred while waiting for memory resources to execute the query in resource pool ‘<something>’ (<number>). Rerun the query.”
You may see these symptoms along with:
- The SQLServer:Memory Manager\Memory Grants Outstanding perfmon counter low
- The SQLServer:Memory Manager\Memory Grants Pending perfmon counter high
- General low memory on the server
Common causes for this include:
- Missing indexes causing large sort or hash operations
- Out-of-date statistics causing incorrectly large cardinality estimates
- Large numbers of concurrent queries running that all require memory to run, or where many of the queries have incorrectly large memory grant requirements
In all these cases, you can use the sys.dm_exec_query_memory_grants DMV to see the size of granted and pending memory grants to identify queries that have or need incorrectly large memory grants and then tune these queries.
If the memory grants look reasonable, I would then check for and troubleshoot general memory shortages on the server. It may also be that Resource Governor is in use and the resource pool memory grant limits are set too low.
Known occurrences in SQL Server (list number matches call stack list):
- Waiting for a query execution memory grant
Abbreviated call stacks (list number matches known occurrences list):
- SOS_Task::PostWait+9e
EventInternal<SuspendQueueSLock>::Wait+1fb
ResourceSemaphore::Acquire+270
CQryMemQueue::CbufAcquireGrant+50a
CQueryResourceGrantManager::AcquireGrant+7c6
CQueryScan::Setup+180
CQuery::CreateExecPlan+9d
CXStmtQuery::SetupQueryScanAndExpression+268
CXStmtQuery::InitForExecute+34
CXStmtQuery::ErsqExecuteQuery+36d
CXStmtSelect::XretExecute+2e7
CMsqlExecContext::ExecuteStmts<1,1>+427
CMsqlExecContext::FExecute+a43
CSQLSource::Execute+86c
process_request+a57
process_commands+4a3
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+ab