2

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

  • You can start your troubleshooting using wait stats but you have to consider other metrics as well. SOS_SCHEDULER yeilds are something where OS forces SQL Server to yeild its scheduler we need to find that reason and that could be any. Are you using physical machine or virtual ? What is version and edition of SQL Server – Shanky Jun 3 '15 at 12:29
  • Please tag the question with the version of SQL Server. – Spörri Jun 3 '15 at 12:30
  • Start by identifying queries with high total_worker_time as identified by the aggregated stats in sys.dm_exec_query_stats and sys.dm_exec_procedure_stats. Signal waits are just a symptom of high CPU and yields are natural occurrence of CPU-bound queries.. – Dan Guzman Jun 3 '15 at 12:36
  • Check this article about handling the SOS_SCHEDULER_YIELD waits: sqlshack.com/… – NikolaD Jun 16 '16 at 10:32
1

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.

  • SQL profiler really ? I guess it would be better if you suggest XE trace or server side trace. We dont know SQL Server version though – Shanky Jun 3 '15 at 12:37
  • Have updated the SQL version in main post – Tom Jun 3 '15 at 12:49
1

Recent expensive queries in the Activity Monitor in SSMS should be your first stop.

For more information grab SP_Whoisactive and find Glenn Berry's diagnostic queries which will help you a lot. The exact query depends on the version of SQLServer but this should be a start.

SELECT TOP(50)
  creation_time
 ,last_execution_time
 ,(total_worker_time+0.0)/1000 AS total_worker_time
 ,(total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime]
 ,execution_count
 ,st.text
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
 WHERE total_worker_time > 0
ORDER BY total_worker_time DESC
  • 1
    I guess its little early to post this as answer :) – Shanky Jun 3 '15 at 12:33
  • This query doesn't return an actual statement? – Tom Jun 3 '15 at 12:50
  • add ,st.text to get the statement text :) - Updated – Spörri Jun 3 '15 at 12:52
  • st.text on it's own is rather hopeless if it's a Stored Procedure as that will return CREATE PROCEDURE..... instead of the actual query. – Mark Sinkinson Jun 3 '15 at 12:54
  • OK this is useful for giving me the queries using the most CPU time. I will look at some execution plans to see if I can see why they take so much time. – Tom Jun 3 '15 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.