(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.)
One of the problems with the SOS_SCHEDULER_YIELD wait type is that it’s not really a wait type. When this wait type occurs, it’s because a thread exhausted its 4ms scheduling quantum and voluntarily yielded the CPU, going directly to the bottom of the Runnable Queue for the scheduler, bypassing the Waiter List. A wait has to be registered though when a thread goes off the processor, so SOS_SCHEDULER_YIELD is used.
You can read more about this wait type:
You want to investigate these waits if they’re a prevalent wait on your server, as they could be an indicator of large scans happening (of data that’s already in memory) where you’d really rather have small index seeks.
The problem is that they’re not a real wait type, so you can’t use my script to look at sys.dm_os_waiting_tasks and get the query plans of threads incurring that wait type, because these threads aren’t waiting for a resource, so don’t show up in the output of sys.dm_os_waiting_tasks!
The solution is to use the sys.dm_exec_requests DMV, as that will show the last_wait_type for all running requests. Below is a script you can use.
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
That will give you the code and query plan of what’s happening, but even with that it might not be obvious which exact operator is causing that wait so you may need to resort to capturing SQL Server call stacks, as I explain in the first blog post link above.
Enjoy!
5 thoughts on “Identifying queries with SOS_SCHEDULER_YIELD waits”
Good post(s), and a good point. I’ve seen a meme that says that “SOS_SCHEDULER_YIELD” waits mean too little CPU, and I used to believe it until I saw that there were machines with CPU at 30-50% that had boatloads of SOS_SCHEDULER_YIELD waits. All this means is a lot of queries are running for a lot more than 4ms.
It’s a bit like CXPacket – with the right traffic, you need to see the right amount of these waits – but not too much. How much is too much? Well… enough that it’s slowing down your response time or throughput!
Oh, okay!. “It depends.” (I’ve been blogging for three months and I’m already hitting the stereotypes.)
Both are signs that you might have queries that are tunable[1] and if you’ve eliminated all other obvious bottlenecks, now’s the time to dig into some of your expensive queries and their plans to see if you can find some juicy indexes or statistics that make a bloated plan slim down. You won’t find much in your short running plans, because they won’t have a lot of SOS_SCHEDULER_YIELD or CXPacket. They don’t run long enough to pick ’em up.
(Well…unless you have a ‘way high Max-DOP, of course. 32 procs can soak up 32x run time in CXPacket, if you’re really unlucky and one thread does all the work.)
[1] Remember, you can tune a query but you can’t… never mind. Old joke.
We have a vendor application upgrade doing a lot of SQL’s and this wait type came up and sat there for 4 hours.
O/S CPU, sp_who and sysprocess showed very little increase in CPU or disk I/O. input buffer showed the query running, but there was nothing I could do to speed it up. I tried setting max DOP to 1 and then back to 8 processes, nothing would work. All I could do was wait it out.
Changing MAXDOP won’t affect queries that are already running. I’d look at the query plan to see whether the query could be changed to use a different plan (unlikely in a vendor app), or maybe add an index to prevent a scan, if that’s what the cause was.
Hi Paul!
There are SOS_SCHEDULER_YIELD wait on our server for long time (10 hour)
when a special table value function is running.
What causes the problem ?
https://www.sqlskills.com/help/waits/sos_scheduler_yield/