Выявление запросов с ожиданиями SOS_SCHEDULER_YIELD

Автор: 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: «Возникает, когда задача добровольно уступает планировщик для выполнения других задач. Во время этого ожидания задача ждёт возобновления своего кванта.»)

Дополнительная информация

Существуют различные шаблонные реакции на этот тип ожидания:

1. «Должно быть, проблема в спинблокировках» — нет, спинблокировки не отслеживаются типами ожиданий.
2. «Должно быть давление на ЦП» — нет, давление на ЦП указывается растущим временем сигнального ожидания (signal-wait times) и длинными очередями выполнения, а не распространённостью ожиданий SOS_SCHEDULER_YIELD.
3. «Запросу нужно больше ЦП» — нет, см. пункт 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

(Номера в списке соответствуют списку стеков вызовов ниже)

1. Уступка во время сканирования таблицы (в данном случае как часть упорядоченного параллельного сканирования таблицы).
2. Уступка во время выполнения сортировки (в данном случае как часть выполнения соединения вложенными циклами).
3. Уступка во время сканирования значений LRU буферов в буферном пуле для заполнения списка свободных буферов (в данном случае при выделении страницы в индексе как часть разбиения страницы).
4. Уступка во время вычисления оценок кардинальности при компиляции плана запроса.
5. Уступка во время сканирования списка буферов для базы данных (в данном случае при завершении работы базы данных как часть DROP DATABASE).
6. И многие, многие другие подобные стеки вызовов из всех частей SQL Server.

Сокращённые стеки вызовов

(Номера в списке соответствуют номерам известных случаев)

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