(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 was able to execute for its full thread quantum (4 milliseconds in all versions of SQL Server, unchangeable), and so voluntarily yielded the scheduler, moving to the bottom of the Runnable Queue in its scheduler. Although the thread is immediately in the RUNNABLE state, it does not go onto the Waiter List because it doesn’t have to wait for a resource. Even though the thread doesn’t need to wait, it must register a wait type when it context switches off the processor, and that wait type is SOS_SCHEDULER_YIELD.
(Books Online description: “Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.”)
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 120 in 2008 and 2008 R2, and 124 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:
There are various knee-jerk reactions to this wait type:
- “It must be spinlocks that are the problem” – no, spinlocks are not tracked by wait types
- “There must be CPU pressure” – no, CPU pressure is indicated by increasing signal-wait times and long Runnable Queues, not by the prevalence of SOS_SCHEDULER_YIELD waits
- “The query needs more CPU” – no, see #2
The most common cause of SOS_SCHEDULER_YIELD waits that I see is queries doing scans of pages that are in memory and aren’t changing, hence there’s no contention for page access and the scanning thread can run until it exhausts its thread quantum. This could be because a query plan is erroneously doing a table scan, or it could be a normal part of your workload. Just like CXPACKET waits, don’t jump to the conclusion that SOS_SCHEDULER_YIELD waits are bad.
I wrote a long article on understanding and troubleshooting SOS_SCHEDULER_YIELD waits on sqlperformance.com that explains more about thread scheduling and quantum exhaustion, plus troubleshooting. Basically this involves identifying the query that’s producing the SOS_SCHEDULER_YIELD waits and making sure the query plan looks correct (e.g. is there a missing nonclustered index causing an in-memory table scan?). Note that queries incurring SOS_SCHEDULER_YIELD waits don’t show up in sys.dm_os_waiting_tasks so you need a script that looks at sys.dm_exec_requests instead – and I have one in this blog post.
When the thread quantum expires, the thread *must* yield the processor. It has no knowledge of other threads on that scheduler and there is *always* a context switch when the thread goes to the bottom of the runnable queue, even if it’s the only thread on the scheduler. The thread cannot decide to just not yield. It’s the context switch that forces the registration of a wait type within SQLOS. If the context switch does not occur (because the thread fails to check whether the quantum has expired), that’s a non-yielding scheduler and you’ll see message 17883 in the error log.
SOS_SCHEDULER_YIELD waits always have a 0 resource wait component, because no resource wait occurs (which is why the thread doesn’t go onto the waiter list).
One more thing to consider is whether your workload is running on a VM that’s experiencing delays because of the host being oversubscribed. This can elevate the number of SOS_SCHEDULER_YIELD waits, as I describe in this post.
Finally, some workloads can suffer from SOS_SCHEDULER_YIELD waits when automatic soft-NUMA is enabled – see this post for details.
Known occurrences in SQL Server (list number matches call stack list):
- Yielding while scanning a table (in this case, as part of an ordered parallel table scan)
- Yielding while performing a sort (in this case, as part of running a nested-loop join)
- Yielding while scanning the LRU values of buffers in the buffer pool to populate the buffer pool free list with free buffers (in this case, while allocating a page in an index as part of a page split)
- Yielding while calculating cardinality estimates while compiling a query plan
- Yielding while scanning the the list of buffers for a database (in this case, while shutting down the database as part of a DROP DATABASE)
And many, many more similar call stacks from all across SQL Server.
Abbreviated call stacks (list number matches known occurrences list):
- SOS_Task::PostWait+90
SOS_Task::Sleep+147
IndexPageManager::GetNextPage+33b
IndexRowScanner::MoveKeyOrderToRowOnNextPage+16c
IndexRowScanner::MoveToRowOnNextPage+23b
IndexDataSetSession::GetNextRowValuesInternal+105b
RowsetNewSS::FetchNextRow+197
CQScanTableScanNew::GetRow+f2
CQScanXProducerNew::GetRowHelper+366
CQScanXProducerNew::GetRow+15
FnProducerOpen+57
FnProducerThread+851
SubprocEntrypoint+a59
SOS_Task::Param::Execute+21e
SOS_Scheduler::RunTask+a8 - SOS_Task::PostWait+90
SOS_Task::Sleep+147
YieldAndCheckForAbort+c3
lmAddCurToList+1d1
lmlink+12c7
soAllocRecBuf+328
RowsetSorted::InsertRow+2b97
RowsetChangeSort::InsertRow+19
CValRowNoHrow::SetDataX+48
CQScanSortNew::PushRow+34
CQScanSortNew::BuildSortTable+28f
CQScanSortNew::OpenHelper+c0
CQScanNLJoinNew::Open+24
CQScanNLJoinNew::Open+24
CQScanNLJoinNew::Open+24
CQScanNLJoinNew::Open+24
CQScanNew::OpenHelper+41
CQScanTopNew::Open+15
CQueryScan::StartupQuery+240
CXStmtQuery::SetupQueryScanAndExpression+2bd
CXStmtQuery::InitForExecute+34 - SOS_Task::PostWait+90
SOS_Task::Sleep+1b2
Worker::OSYieldNoAbort+2f
BPool::ReplenishFreeList+561
BPool::Steal+52f
BPool::NewPage+7af
PageRef::SetupPageHeaderPreAllocation+64
SetupPageHeaderPreAllocation+6c
TargetExtentMgr::AllocPageFromTargetExtent+5cd
AllocationReq::AllocateFromExistingExtents+9e1
AllocationReq::AllocatePages+15ca
AllocationReq::Allocate+f3
AllocateHoBtDataPage+5fd
IndexPageManager::AllocatePage+1b8
SplitPage+b59
IndexDataSetSession::InsertSmallRecord+1b5c
IndexDataSetSession::InsertRowInternal+2d11
DatasetSession::InsertRow+163
RowsetNewSS::InsertRow+26
CBlobHandleFactoryMain::CreateNewBlobHandleInternal+1ec
CBlobHandleFactoryMain::CreateNewBlobHandle+88
CBlobHandleHelper::CopyBlobHandleIntoTempOrInlined+1c5 - SOS_Task::PostWait+90
SOS_Task::Sleep+ea
YieldAndCheckForAbort+ec
BuildDensityMap+26
OptimizerUtil::CalculateDistinctCardUsingDensity+1da
OptimizerUtil::GroupCardDistinctHelper+8d2
CCostUtils::CalcLoopJoinCachedInfo+2036
CCostUtils::PcctxLoopJoinHelper+124
CTask_OptInputs::Perform+680
CMemo::ExecuteTasks+17c
CMemo::PerformOptimizationStage+347
CMemo::OptimizeQuery+9db
COptContext::PexprSearchPlan+131
COptContext::PcxteOptimizeQuery+24b8
COptContext::PqteOptimizeWrapper+221
PqoBuild+db6
CStmtQuery::InitQuery+27f
CStmtSelect::Init+ee
CCompPlan::FCompileStep+1844
CSQLSource::FCompile+13f7
CSQLSource::FCompWrapper+d3
CSQLSource::Transform+445 - SOS_Task::PostWait+90
SOS_Task::Sleep+1b2
Worker::OSYieldNoAbort+2f
BufIter::GetNext+100
BPool::RemoveDatabaseByFileId+c6
RecoveryUnit::Shutdown+14c
DBTABLE::Shutdown+179
DBMgr::ShutdownDB+1b1
NotifyEndDropDatabase::HandleEvent+69
XactRM::FireNotificationsInternal+cf
XactRM::SinglePhaseCommit+558
XactRM::CommitInternal+4b9
FullXactImp::Commit+326
CMsqlXactInternalReadWrite::Commit+15
CMsqlXactImp::Commit+1d2
CAutoMsqlXact::CommitNestedXact+bc
CStmtDropDB::XretExecute+bb1
CMsqlExecContext::ExecuteStmts<0,1>+864
CMsqlExecContext::FExecute+a48
CSQLSource::Execute+866