SOS_SCHEDULER_YIELD 等待故障排除

執行我們的企業 ERP (Dynamics AX 2012)，我注意到我們的生產環境似乎比我們的開發系統慢得多。

在執行跟踪的同時在開發環境和生產環境中執行相同的活動後，我確認 SQL 查詢在我們的生產環境中的執行速度比開發環境慢（平均慢 10-50 倍）。

起初我將此歸因於負載，並在下班時間在生產環境中重新執行相同的活動，並在跟踪中發現相同的結果。

我在 SQL Server 中清除了等待統計資訊，然後讓伺服器在其正常的生產負載下執行一段時間，然後執行以下查詢：

WITH [Waits] AS
   (SELECT
       [wait_type],
       [wait_time_ms] / 1000.0 AS [WaitS],
       ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
       [signal_wait_time_ms] / 1000.0 AS [SignalS],
       [waiting_tasks_count] AS [WaitCount],
       100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
       ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
   FROM sys.dm_os_wait_stats
   WHERE [wait_type] NOT IN (
       N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
       N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
       N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
       N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
       N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
       N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
       N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
       N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
       N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
       N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
       N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
       N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
       N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
   )
SELECT
   [W1].[wait_type] AS [WaitType],
   CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
   CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
   CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
   [W1].[WaitCount] AS [WaitCount],
   CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
   CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
   CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
   CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
   [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold

我的結果如下：

WaitType               Wait_S  Resource_S  Signal_S  WaitCount  Percentage  AvgWait_S  AvgRes_S  AvgSig_S
SOS_SCHEDULER_YIELD   4162.52        3.64   4158.88    4450085       77.33     0.0009    0.0000    0.0009
ASYNC_NETWORK_IO       457.98      331.59    126.39     351113        8.51     0.0013    0.0009    0.0004
PAGELATCH_EX           252.94        5.14    247.80     796348        4.70     0.0003    0.0000    0.0003
WRITELOG               166.01       48.01    118.00     302209        3.08     0.0005    0.0002    0.0004
LCK_M_U                145.47      145.45      0.02        123        2.70     1.1827    1.1825    0.0002

所以看起來最大的等待是 SOS_Scheduler_Yield 到目前為止，我搜尋了一下發現它通常與 CPU 無法跟上。

然後我連續多次執行此查詢。

SELECT *
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

我知道我應該尋找具有非零runnable_tasks_count 或pending_disk_io_count 的調度程序，但它幾乎一直都是零。

我還應該提到，最大並行度設置為 1，因為 Dynamics AX 工作負載本質上通常是 OLTP，並且將其更改為 8 並沒有對上述等待統計數據產生太大影響，它們變得幾乎完全相同性能問題。

我有點不知道從哪裡開始，我基本上有一個看似 CPU 緊張但不等待 runnable_tasks 或 IO 的 SQL Server。

我確實知道這個 SQL Server 的 IO 子系統不是很好，因為在包含實際數據庫的驅動器上執行 SQLIO 可能會導致非常低的數字（對於某些類型的讀/寫，認為每秒 10MB），也就是說，由於伺服器上記憶體大多數數據庫的記憶體量，SQL 似乎沒有在等待它。

這裡有一些環境資訊可以提供幫助：

生產環境：

• SQL 伺服器
• HP ProLian DL360p Gen8
• Intel Xeon E5-2650 0 @ 2.00GHz x 2 超執行緒（32 個邏輯核心）
• 184GB記憶體
• 視窗伺服器 2012
• 2 個 SQL Server 2012 Standard 實例（RTM，未打更新檔）
• Raid 1 279GB 驅動器 (15k) C：驅動器，包含數據庫和作業系統
• 頁面文件和 TempDB 位於不同的獨立驅動器上（固態）

我的開發者：

• Hyper-V 託管 SQL Server 和 Dynamics AX 2012 AOS 伺服器
• Core i7 3.4ghz 超執行緒（8 個邏輯核心）
• 8GB記憶體
• 視窗伺服器 2008 R2
• 整個 VM 的 SSD。

我歡迎任何關於其他事情的意見。

所以我解決了這個問題，結果發現我們的 SQL 伺服器上啟用了電源管理功能，這些功能正在上下調整 CPU 頻率，但速度不足以跟上小需求，並引入了 SOS_Scheduler_Yield 等待。在將其更改為始終以高性能執行後，問題就消失了，現在等待更加正常（LatchIO 類型的東西）。

引用自：https://dba.stackexchange.com/questions/42827