Troubleshooting SOS_SCHEDULER_YIELD wait

字数 5240阅读 1726评论 0赞 0

<table><tbody><tr><td>6<a>down vote</a><a href="http://dba.stackexchange.com/questions/42827/troubleshooting-sos-scheduler-yield-wait#" target="_blank">favorite</a>1 </td><td>Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems.After performing the same activities in both the development and production environments while running a trace, I confirmed that SQL queries were executing very slowly on our production environment compared to development (10-50x slower on average).At first I attributed this to load, and re-ran the same activities on the production environment during off hours and found the same results in the trace.I cleared my wait stats in SQL Server then let the server run under its normal production load for a little while, and then ran this query: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
My results are as follows: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
So seemingly the largest Wait is SOS_Scheduler_Yield by far, and I googled around and found it typically relates to the CPU not being able to keep up.I then ran this query multiple times in succession.SELECT *
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
I know I'm supposed to be looking for schedulers with non-zero runnable_tasks_count or pending_disk_io_count, but it's basically zero almost all the time.I should also mention that Max Degree of Parallelism was set to 1, since the Dynamics AX workload is typically OLTP in nature, and changing it 8 did not make much of difference in the above wait stats, they became almost the exact same with the same performance problems.I'm sort of at a loss of where to go from here, I basically have a SQL Server that is seemingly CPU strapped but not waiting on runnable_tasks or IO.I do know that the IO subsystem of this SQL Server isn't very good, because running SQLIO on the drive containing the actual databases can lead to pretty low numbers (think 10MB a sec for certain types of reads/write), that said, it doesn't seem like SQL is waiting on that because of the amount of memory on the server caching most of the databases.Here is some environment information to help:Production environment:<ul><li>SQL Server</li><li>HP ProLian DL360p Gen8</li><li>Intel Xeon E5-2650 0 @ 2.00GHz x 2 with hyperthreading (32 logical cores)</li><li>184GB memory</li><li>Windows Server 2012</li><li>2 instances of SQL Server 2012 Standard (RTM, unpatched)</li><li>Raid 1 279GB drives (15k) C: drive, contains databases and operating system</li><li>Page File and TempDB on distinct,seperate drives (solid state)</li></ul>My DEV:<ul><li>Hyper-V hosted SQL Server and Dynamics AX 2012 AOS server</li><li>Core i7 3.4ghz with hyperthreading (8 logical cores)</li><li>8GB of memory</li><li>Windows Server 2008 R2</li><li>SSD for the entire VM.</li></ul> </td></tr></tbody></table>

数据库

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

Troubleshooting SOS_SCHEDULER_YIELD wait

添加新评论0 条评论

作者其他文章

相关文章

相关问题

相关资料