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

RESOURCE_SEMAPHORE_QUERY_COMPILE

(Back to main page…)

Description:

This wait type is when a thread is waiting for availability at the next higher query compilation memory gateway. Queries start compiling with no memory limit, and as a query’s compilation takes more and more memory, compilation will be stalled at each of three gateways (small, medium, and large) that limit the number of concurrently compiling queries that are using more than a set amount of compilation memory (which increases from the small to the large gateway). This effectively throttles the amount of memory that can be consumed by query compilation at any time.

For instance, on x64 servers, a compiling query must pass through the small gateway once it requires 380,000 bytes of compile memory. The thresholds for the medium and large gateways are dynamically set based on the server load.

(Books Online description: “Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.”)

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 283 in 2008 and 2008 R2, 290 in 2012, and 297 in 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 compiling queries cannot get the memory they need to continue, as memory usage for concurrent compilations is capped, as described above.

You may see these waits along with:

  • The SQLServer:Memory Manager\SQL Compilations/sec perfmon counter high
  • The SQLServer:Memory Manager\SQL Recompilations/sec perfmon counter high
  • High number of single use plans in the plan cache

From a query compilation perspective, you can reduce the amount of compilation or recompilation by:

  • Making sure that Resource Governor memory limits are not incorrectly set
  • Rewrite queries to reduce compilation memory requirements (look in the plan cache for queries requiring a lot of compile memory using Jonathan’s code)
  • Improve plan reuse (avoiding each query needing its own compiled plan) through parameterization and use of stored procedures – see Kimberly’s post
  • Reduce compilations by use plan guides for commonly-executed ad hoc queries
  • Avoid overuse of WITH RECOMPILE
  • Avoid using AUTO_CLOSE, which forces all plans for a database to be dropped from the plan cache

Note that there is also a bug in SQL Server 2014 that can cause excessive waits of this type – see KB 3024815.

Known occurrences in SQL Server (list number matches call stack list):

All stacks: waiting for the semaphore for a query compilation gateway.

And many more similar call stacks.

Abbreviated call stacks (list number matches known occurrences list):

  1. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+25e
    ResourceSemaphore::Acquire+285
    CQPCompilationPool::AcquireSemaphore+409
    CQPCompilationQueue::AcquireSemaphore+40
    CQPCompilationQueue::QPCompileYieldCallback+ff
    COpArg::DeriveNormalizedGroupProperties+48
    COptExpr::DeriveGroupProperties+187
    COptExpr::DeriveGroupProperties+102
    COptExpr::DeriveGroupProperties+102
    COptExpr::PexprFold+cd
    CSubRuleSELonUN::BuildSubstitutes+344
    COptContext::PexprTransformTopLevel+4ff
    COptContext::PexprNormalize+f5
    COptContext::PexprNormalize+3c1
    COptContext::PexprNormalize+3c1
    COptContext::NormalizeQuery+f5
    COptContext::PexprSimplify+174
    COptContext::PcxteOptimizeQuery+ecd
    COptContext::PqteOptimizeWrapper+224
    PqoBuild+e28
    CStmtQuery::InitQuery+28d
  2. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+25e
    ResourceSemaphore::Acquire+285
    CQPCompilationPool::AcquireSemaphore+409
    CQPCompilationQueue::AcquireSemaphore+40
    CQPCompilationQueue::QPCompileYieldCallback+ff
    CParser::FillBuffer+18b
    CParser::CParser+4ba
    sqlpars+124
    CSQLSource::FParse+296
    CSQLSource::FCompWrapper+f7
    CSQLSource::Transform+5c2
    CSQLStrings::PtrGetCmd+12c
    FLoadDefault+3f0
    CBindEnv::FLoadDefault+6b
    CRelOp_DML::PexprProcessOneImplicitColumn+30b
    CRelOp_DML::ProcessImplicitColumns+250
    CRelOp_Insert::BindSelfSecondary+976
    CRelOp_Insert::BindSelfPrimary+51
    CRelOp_Insert::BindTree+184
    COptExpr::BindTree+85
    CRelOp_DMLQuery::BindTree+5f
  3. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+25e
    ResourceSemaphore::Acquire+285
    CQPCompilationPool::AcquireSemaphore+409
    CQPCompilationQueue::AcquireSemaphore+40
    CQPCompilationQueue::QPCompileYieldCallback+ff
    OptimizerUtil::AddScalarOnlyChildrenToMemo+aa
    CStatsCollectionCache_Filter::PCreateKey+7b
    CCardFrameworkSQL12::DeriveCardinalityProperties+fa
    CLogOpArg::DeriveGroupProperties+740
    COpArg::DeriveNormalizedGroupProperties+285
    COptExpr::DeriveGroupProperties+187
    COptExpr::DeriveGroupProperties+102
    COptExpr::DeriveGroupProperties+102
    COptExpr::DeriveGroupProperties+102
    COptContext::PexprSimplify+bad
    COptContext::PcxteOptimizeQuery+ecd
    COptContext::PqteOptimizeWrapper+224
    PqoBuild+e28
    CStmtQuery::InitQuery+28d
    CStmtDML::InitNormal+4eb
    CStmtDML::Init+11