(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 switching to preemptive mode while allocating memory that has previously been reserved for the query (i.e. the query execution memory grant). The preemptive switch is so that the code being executed does not have to check for quantum exhaustion.
(Books Online description: N/A)
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:
2016
Removed in SQL Server version:
N/A
Extended Events wait_type value:
The map_key value in sys.dm_xe_map_values is 1101 in 2016 RTM. After 2016 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.
Other information:
This wait type can occur in lots of different places within SQL Server.
As one example, the blog post at https://orderbyselectnull.com/2018/03/06/large-cci-etls-cannot-scale-without-tf-834/ demonstrates how this wait type can become a bottleneck when scaling a clustered columnstore index workload due to contention for memory resources, and workarounds are to enable large page support using trace flag 834 or to rework the workload to reduce overall memory usage while performing concurrent columnstore operations. However, please do not think this wait type is limited to columnstore at all.
Known occurrences in SQL Server (list number matches call stack list):
- Allocating reserved memory (in this case, while inserting into a workfile)
- Allocating reserved memory (in this case, while inserting into a sort run)
- Allocating reserved memory (in this case, while inserting into a partitioned workfile for a query hash)
And many more similar call stacks.
Abbreviated call stacks (list number matches known occurrences list):
- SOS_Task::PopWait+0xb4
MemoryClerkInternal::AllocateReservedPages+0x147
BPool::Steal+0x3d1
PbufGetGrantedBufs+0x82
WFRSRCMGR::GetExtentBuf+0x173
WORKFILE::GetNewCrntBuf+0x26
WORKFILE::AllocRowBuf+0xa8
RowsetWorkFile::InsertRow+0x1e6c
CValRowHash::SetDataX+0x2b8
CEsExec::GeneralEval+0x1e9
CHashWorkfilePartitionGroup::Insert+0x303
CQScanHash::ConsumeBuild+0x369
CQScanHash::Open+0x8e
CQScanNLJoinNew::Open+0x27
CQScanNLJoinNew::Open+0x27
CQScanSortNew::BuildSortTable+0x36
CQScanSortNew::OpenHelper+0xbc
CQScanNew::OpenHelper+0x41
CQScanStreamAggregateNew::Open+0x39
CQScanConcatNew::GetRow+0x86
CQueryScan::GetRow+0x81
CXStmtQuery::ErsqExecuteQuery+0x6c9 - SOS_Task::PopWait+0xb4
MemoryClerkInternal::AllocateReservedPages+0x147
BPool::Steal+0x3d1
PbufGetGrantedBufs+0x82
soGetBuf+0xac
soAllocRecBuf+0x3c1
RowsetSorted::InsertRow+0x1e8b
CValRowNoHrow::SetDataX+0x48
CEsExec::GeneralEval+0x1e9
CQScanSortNew::BuildSortTable+0x227
CQScanSortNew::OpenHelper+0xbc
CQScanNew::OpenHelper+0x41
CQScanStreamAggregateNew::Open+0x39
CQScanNew::OpenHelper+0x41
CQScanStreamAggregateNew::Open+0x39
CQueryScan::UncacheQuery+0x36f
CXStmtQuery::SetupQueryScanAndExpression+0x365
CXStmtQuery::InitForExecute+0x34
CXStmtQuery::ErsqExecuteQuery+0x4df
CXStmtSelect::XretExecute+0x322
CMsqlExecContext::ExecuteStmts<1,1>+0x40d
CMsqlExecContext::FExecute+0xa9e - SOS_Task::PopWait+0xb4
MemoryClerkInternal::AllocateReservedPages+0x147
PvGetGrantedPage+0x6b
CHashWorkfilePartitionInstance::PvAllocateHashBucketPage+0x1f
CHashWorkfilePartitionInstance::PhiGetItemWithAlloc+0x51
CHashWorkfilePartitionInstance::AppendHashItem+0x12
CHashWorkfilePartitionGroup::Insert+0x2a6
CQScanHash::ConsumeBuild+0x369
CQScanHash::Open+0x8e
CQScanNew::OpenHelper+0x41
CQScanNew::OpenHelper+0x41
CQScanStreamAggregateNew::Open+0x39
CQueryScan::StartupQuery+0x2a7
CXStmtQuery::SetupQueryScanAndExpression+0x33e
CXStmtQuery::InitForExecute+0x34
CXStmtQuery::ErsqExecuteQuery+0x4df
CXStmtSelect::XretExecute+0x322
CMsqlExecContext::ExecuteStmts<1,1>+0x40d
CMsqlExecContext::FExecute+0xa9e
CSQLSource::Execute+0x981
process_request+0xe61
process_commands_internal+0x2df