Автор: Paul Randal, Identifying queries with SOS_SCHEDULER_YIELD waits
Одна из проблем с типом ожидания SOS_SCHEDULER_YIELD заключается в том, что это на самом деле не тип ожидания. Когда возникает этот тип ожидания, это происходит потому, что поток исчерпал свой 4-миллисекундный квант планирования и добровольно уступил ЦП, перейдя непосредственно в конец очереди выполнения (Runnable Queue) для планировщика, минуя список ожидания (Waiter List). Однако ожидание должно быть зарегистрировано, когда поток покидает процессор, поэтому используется SOS_SCHEDULER_YIELD.
Эти ожидания стоит исследовать, если они преобладают на вашем сервере, так как они могут указывать на выполнение больших сканирований данных, которые уже находятся в памяти, в то время как вы бы предпочли маленькие поиски по индексу.
Проблема в том, что они не являются настоящим типом ожидания, поэтому вы не можете использовать мой скрипт для просмотра sys.dm_os_waiting_tasks и получения планов запросов потоков, вызывающих этот тип ожидания, потому что эти потоки не ожидают ресурса и поэтому не отображаются в выводе sys.dm_os_waiting_tasks!
Решение — использовать динамическое административное представление sys.dm_exec_requests, так как оно показывает last_wait_type для всех выполняющихся запросов. Ниже приведён скрипт, который можно использовать.
SELECT
[er].[session_id],
[es].[program_name],
[est].text,
[er].[database_id],
[eqp].[query_plan],
[er].[cpu_time]
FROM sys.dm_exec_requests [er]
INNER JOIN sys.dm_exec_sessions [es] ON
[es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
[es].[is_user_process] = 1
AND [er].[last_Wait_type] = N'SOS_SCHEDULER_YIELD'
ORDER BY
[er].[session_id];
GO
Этот скрипт покажет вам код и план запроса того, что происходит, но даже с этим может быть неочевидно, какой именно оператор вызывает это ожидание, поэтому вам может потребоваться прибегнуть к захвату стеков вызовов SQL Server, как я объясняю в ссылке на блог выше.
Описание типа ожидания SOS_SCHEDULER_YIELD
Этот тип ожидания возникает, когда поток смог выполниться в течение полного кванта времени (4 миллисекунды во всех версиях SQL Server, неизменяемо) и поэтому добровольно уступил планировщик, переместившись в конец очереди выполнения (Runnable Queue) своего планировщика. Хотя поток сразу переходит в состояние RUNNABLE, он не попадает в список ожидания (Waiter List), потому что ему не нужно ожидать ресурса. Несмотря на то, что потоку не нужно ждать, он должен зарегистрировать тип ожидания при переключении контекста с процессора, и этим типом является SOS_SCHEDULER_YIELD.
(Описание из Books Online: «Возникает, когда задача добровольно уступает планировщик для выполнения других задач. Во время этого ожидания задача ждёт возобновления своего кванта.»)
Дополнительная информация
Существуют различные шаблонные реакции на этот тип ожидания:
- «Должно быть, проблема в спинблокировках» — нет, спинблокировки не отслеживаются типами ожиданий.
- «Должно быть давление на ЦП» — нет, давление на ЦП указывается растущим временем сигнального ожидания (signal-wait times) и длинными очередями выполнения, а не распространённостью ожиданий
SOS_SCHEDULER_YIELD. - «Запросу нужно больше ЦП» — нет, см. пункт 2.
Наиболее распространённая причина ожиданий SOS_SCHEDULER_YIELD, которую я вижу, — это запросы, выполняющие сканирование страниц, которые находятся в памяти и не изменяются, следовательно, нет конкуренции за доступ к страницам, и поток сканирования может выполняться, пока не исчерпает свой квант времени. Это может быть связано с тем, что план запроса ошибочно выполняет сканирование таблицы, или это может быть нормальной частью вашей рабочей нагрузки. Как и в случае с ожиданиями CXPACKET, не делайте поспешного вывода, что ожидания SOS_SCHEDULER_YIELD — это плохо.
Я написал большую статью о понимании и устранении ожиданий SOS_SCHEDULER_YIELD на sqlperformance.com, которая более подробно объясняет планирование потоков и исчерпание квантов, а также устранение неполадок. В основном это включает идентификацию запроса, который вызывает ожидания SOS_SCHEDULER_YIELD, и проверку того, что план запроса выглядит корректным (например, отсутствует ли некластерный индекс, вызывающий сканирование таблицы в памяти?). Обратите внимание, что запросы, вызывающие ожидания SOS_SCHEDULER_YIELD, не отображаются в sys.dm_os_waiting_tasks, поэтому вам нужен скрипт, который использует sys.dm_exec_requests — и такой скрипт есть в этой статье выше.
Когда квант времени потока истекает, поток обязан уступить процессор. Он не имеет информации о других потоках на этом планировщике, и всегда происходит переключение контекста, когда поток переходит в конец очереди выполнения, даже если он единственный поток на планировщике. Поток не может решить просто не уступать. Именно переключение контекста заставляет регистрировать тип ожидания внутри SQLOS. Если переключение контекста не происходит (потому что поток не проверяет, истёк ли квант), это невыполняющий уступку планировщик (non-yielding scheduler), и вы увидите сообщение 17883 в журнале ошибок.
Ожидания SOS_SCHEDULER_YIELD всегда имеют нулевой компонент ожидания ресурса (0 resource wait component), потому что ожидания ресурса не происходит (именно поэтому поток не попадает в список ожидания).
Ещё одна вещь, которую следует учитывать, — работает ли ваша рабочая нагрузка на виртуальной машине, которая испытывает задержки из-за переподписки хоста. Это может повысить количество ожиданий SOS_SCHEDULER_YIELD, как описано в этой статье.
Наконец, некоторые рабочие нагрузки могут страдать от ожиданий SOS_SCHEDULER_YIELD, когда включена автоматическая программная NUMA (soft-NUMA) — подробности см. в этой статье.
Известные случаи в SQL Server
(Номера в списке соответствуют списку стеков вызовов ниже)
- Уступка во время сканирования таблицы (в данном случае как часть упорядоченного параллельного сканирования таблицы).
- Уступка во время выполнения сортировки (в данном случае как часть выполнения соединения вложенными циклами).
- Уступка во время сканирования значений LRU буферов в буферном пуле для заполнения списка свободных буферов (в данном случае при выделении страницы в индексе как часть разбиения страницы).
- Уступка во время вычисления оценок кардинальности при компиляции плана запроса.
- Уступка во время сканирования списка буферов для базы данных (в данном случае при завершении работы базы данных как часть
DROP DATABASE). - И многие, многие другие подобные стеки вызовов из всех частей SQL Server.
Сокращённые стеки вызовов
(Номера в списке соответствуют номерам известных случаев)
-
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
