2

Is there a way to capture the queries experiencing the SOS_Scheduler_Yield wait type as it is happening. My cpu is pegging out at night and I suspect its some xml processing that is causing it by chewing up all my processor cycles; however, the application design is such that what is coming back as having the highest worker time is a section of xml that should only be run one time per client. So I need to catch the individual offending query each night to make my case.

Lumpy
  • 2,109
  • 8
  • 32
  • 43
  • Have you considered XEvents trace or persist WhoIsActive into a table? Run it during the troubled times and then analyze the output. – DenisT Nov 01 '17 at 13:10
  • @DenisT - Sorry not overly familiar with extended events but I can look into setting that up. – Lumpy Nov 01 '17 at 13:11
  • are you familiar with sp_WhoIsActive -- http://whoisactive.com/? It's super easy to set up to collect data into a table for a period of time every whatever seconds. It's super lightweight as well. – DenisT Nov 01 '17 at 13:44

1 Answers1

2

The Extended Events option is by far the easiest solutions, once you get used to Extended Events.

Explanation

An article on SQLPerformance.com goes into the specifics behind this wait type...

... So there is one cause for SOS_SCHEDULER_YIELD: a thread exhausting its scheduling quantum and heavily recurring instances can lead to SOS_SCHEDULER_YIELD being the most prevalent wait along with high CPU usage. ... The only way to prove what's causing SOS_SCHEDULER_YIELD waits is to capture SQL Server call stacks when that wait type occurs, using Extended Events and debug symbols from Microsoft. I have a blog post that describes and shows how to perform that investigation, and there’s a great whitepaper about spinlocks and spinlock investigations that is worth reading if you’re interested in that depth of internals.

Reference: Knee-Jerk Wait Statistics : SOS_SCHEDULER_YIELD

It also relates to how you can perform an analysis (blog in above quote):

Reference: SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock (SQLSkills.com)

Extended Events

Add an Extended Event to your instance with the following code:

CREATE EVENT SESSION [Capture_Statements_SOS_SCHEDULER_YIELD] ON SERVER 
ADD EVENT sqlos.wait_info(
    ACTION(sqlserver.client_hostname,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([wait_type]=(123))) 
ADD TARGET package0.event_file(SET filename=N'C:\Temp\Capture_Statements_SOS_SCHEDULER_YIELD.xel')
WITH (STARTUP_STATE=ON)
GO

Caution
Check the filename and modify to your preference!
Check the STARTUP_STATE!
Modify the Extended Event to your preferences when finished.

Reference: Extended Events (Microsoft Docs)

Quick solution

A quick solution is to just run the following script when you are having issues:

SELECT sdow.session_id,
       sdow.wait_type,
       sder.status,
       sder.sql_handle,
       DB_NAME(sder.database_id),
       dest.text
FROM   sys.dm_os_waiting_tasks    AS sdow
       JOIN sys.dm_exec_requests  AS sder
            ON  sdow.session_id = sder.session_id
       CROSS APPLY sys.dm_exec_sql_text(sder.sql_handle) AS dest
WHERE  sdow.wait_type = 'SOS_SCHEDULER_YIELD'

References

John K. N.
  • 17,649
  • 12
  • 51
  • 110