RESERVED_MEMORY_ALLOCATION_EXT

(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

(Back to main page…)

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):

  1. Allocating reserved memory (in this case, while inserting into a workfile)
  2. Allocating reserved memory (in this case, while inserting into a sort run)
  3. 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):

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