Sql-server – High SOS_SCHEDULER_YIELD and Signal Waits

performancesql-server-2008-r2wait-types

I have run Paul Randals wait script and can see SOS_SCHEDULER_YIELD is at 60% with an average signal wait of 10ms. I have also run a script to find out the what percentage of my waits are signal waits and in total it is 47%.

I can seen in task manager that the CPU usage is high and I believe I do have a CPU issue, but how do I find which queries are the ones causing the signal waits?

Just to update: The version of SQL is 2008R2

Best Answer

First of all, signal wait time is the time a query is ready to run, but waiting for a CPU "slot" to be ready.

When you are under CPU Pressure everything will be slowed down. To determine which query causes issues you can use SQL Server Profiler and save the trace to files. Later you can load this trace files into a cool tool called Clear Trace. It will filter out not needed data from the tracefiles and orders your calls by whatever property you want. Of course CPU time is one of them.

This link will help you achieving it:

http://www.brentozar.com/archive/2012/06/queries-killing-my-server-training-video/

Since this tool also tells you how often queries are run, you might catch queries which are taking just a bit of cpu time but are executed millions of time. These you might miss using different approaches.

Related Question