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

SOS_SCHEDULER_YIELD

(Back to main page…)

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:

  1. “It must be spinlocks that are the problem” – no, spinlocks are not tracked by wait types
  2. “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
  3. “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).

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

  1. Yielding while scanning a table (in this case, as part of an ordered parallel table scan)
  2. Yielding while performing a sort (in this case, as part of running a nested-loop join)
  3. 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)
  4. Yielding while calculating cardinality estimates while compiling a query plan
  5. 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):

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