RESOURCE_SEMAPHORE_QUERY_COMPILE

(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 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.

In SQL Server 2014 SP1+CU1 or SQL Server 2014 CU6 for RTM and higher, you can use trace flag 6498 to allow more than one large gateway, which can alleviate this wait.  From SQL Server 2016 onward, no trace flag is required. More information is in 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