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 類型的東西)。