This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SOS_SCHEDULER_YIELD is a kind of wait type, but you cannot locate in the output of sys.dm_os_wait_stats.
The SOS_SCHEDULER_YIELD wait means that an executing thread voluntarily gave up the CPU to allow other threads to execute.
When a large number of data scan is happening, you can investigate these waits which generate prevalent wait on your server.
Use below script, to check SOS_SCHEDULER_YIELD for all running queries.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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] |
I understood from the sqlskills.com:
Leave a Reply