1

I am troubleshooting a particular server and collected the following wait stats information:

enter image description here

The script I used to collect the information is here: https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Looking at SOS_SCHEDULER_YIELD, there is a corresponding high value for signal waits, does this mean that CPU pressure is in fact the issue here?

I know that CPU pressure is usually thought of as the culprit, but I have read that in a lot of cases, it's not actually the case and further investigation is needed.

I have read that a process running on a CPU has a maximum time of 4ms that it is allowed to run and if it exhausts this time, it gives up its CPU to another thread.

This statement confuses me. I am sure there is more to it. Does this mean that if after 4ms, it has not done anything, it gives up its CPU?

At the time of posting, there was one particular query running with a wait type of SOS_SCHEDULER_YIELD. The query plan indicated a large table scan with a high value missing index.

The second result in the picture shows signal waits at 7.39% - This doesn't correspond to what I am seeing in the first result. Am I missing something in my understanding?


This is the query I was using to get the overall signal wait vs resource waits:

SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [%resource waits] FROM sys.dm_os_wait_stats
OPTION (RECOMPILE);

I have updated this script to filter out irrelevant wait types and it yielded this result:

  • %signal (CPU Waits): 92.31
  • %resource waits: 7.69
0

3 Answers 3

4

Note you should always include the query that was used to obtain the results. This doesn't really tell us much other than that SOS_SCHEDULER_YIELD appears to be your highest wait type.

This is not abnormal and not necessarily indicative of an issue or CPU pressure, by itself. You should read up on the SQLSkills.com link, included in your result set, about it.

Some relevant information from that link (emphasized by me):

There are various knee-jerk reactions to this wait type:

  1. “It must be spinlocks that are the problem” – no, spinlocks are not tracked by wait types
  2. “There must be CPU pressure” – no, CPU pressure is indicated by increasing signal-wait times and long Runnable Queues, not by the prevalence of SOS_SCHEDULER_YIELD waits
  3. “The query needs more CPU” – no, see #2

The most common cause of SOS_SCHEDULER_YIELD waits that I see is queries doing scans of pages that are in memory and aren’t changing, hence there’s no contention for page access and the scanning thread can run until it exhausts its thread quantum. This could be because a query plan is erroneously doing a table scan, or it could be a normal part of your workload. Just like CXPACKET waits, don’t jump to the conclusion that SOS_SCHEDULER_YIELD waits are bad.

So to summarize, you don't necessarily have CPU pressure just because you see the SOS_SCHEDULER_YIELD wait type. You may have queries doing Scan operations instead of Index Seeks that can potentially be tuned to be more performant though.

If you don't know what your potentially problematic queries are, you can use Brent Ozar's First Responder Kit to help you find them. sp_BlitzCache is a good start for finding heavy query plans, including ones that use a lot of CPU. You can sort by CPU by executing dbo.sp_BlitzCache @SortOrder = 'CPU';.

You can also use Erik Darling's sp_PressureDetector to help determine when you have CPU pressure. It gives some quick metrics on how many threads there are in total, how many are currently being used, how many remain available, and how many are currently waiting for CPU resources. It'll also tell you the wait type and duration for each process.

To your question on how CPU quantum works:

I have read that a process running on a CPU has a maximum time of 4ms that it is allowed to run and if it exhausts this time, it gives up its CPU to another thread. This statement confuses me and I am sure there is more to it. Does this mean that if after 4ms, it has not done anything, it gives up its CPU?

When a thread has needed more than 4ms of CPU time to do it's work, a context switch occurs such that that thread is put in the back of a queue, and the next thread up on the queue is given CPU resources. This is normal behavior to allow multiple processes to be able to accomplish work concurrently. If CPU concurrency wasn't designed this way and a single process needed 1 minute of total CPU time then all other processes would need to wait a full minute before they could do any work, and it would be an inefficient way to manage CPU resources.

The Windows operating system works with the same purpose in mind (used to be the same methodology). Imagine if any action you did on your computer, such as running a program, locked up the entire system (even your mouse cursor couldn't move on the screen) until that program finished running. It would be a terrible user experience. So instead, there is this methodology of sharing CPU resources to each process. Per Dan, in modern versions of Windows, the processes actually interrupt each other instead of waiting on quantum completion and queuing, but the idea of being able to allow multiple processes to concurrently complete some of their work is the same.

This is not to be confused with parallelism which truly runs multiple processes simultaneously across multiple CPU cores. The former with CPU thread quantum concurrency works even on a single threaded CPU system.


Per your questions in the comments:

This "wait" is SOS_Scheduler_Yield. Why specifically are scans looked at for high values for this wait type? Why not any other long running operator?

Other long running operations likely have a specific wait type already defined for them, because they can be caused by distinctive issues. SOS_SCHEDULER_YIELD is a more generic wait type describing a normal process, a query whose thread took more than 4ms to run. There's nothing unusual about that.

But there are long running operations that can potentially be tuned to be faster such as Scans when a better index can be defined that results in Index Seeks instead. And not all Scans are bad, sometimes that is the most efficient operation to serve a particular query (it just depends on the goal of the query).

So while most wait types indicate a problem and visa versa, SOS_SCHEDULER_YIELD doesn't necessarily indicate a problem rather it describes a normal behavior. A specific set of query inefficiencies can cause a query to run longer which can then result in many occurrences of SOS_SCHEDULER_YIELD, but the reverse isn't necessarily true (a lot of SOS_SCHEDULER_YIELD occurrences doesn't necessarily mean there's a problem).

3
  • Thank you The script I used to collect the information is here: sqlskills.com/blogs/paul/… So if I understand this correctly, a query starts running on a CPU, after 4ms, stops, goes to the bottom of the runnable queue where it waits to make its way back to the top, rinse & repeat until the query completes. This "wait" is SOS_Scheduler_Yield. Why specifically are scans looked at for high values for this wait type? Why not any other long running operator? Oct 24, 2022 at 13:43
  • Correct. Many other operations like waiting for a lock or I/O operations means that the session is put in a different query and wait for that particular synchronization (lock to be released, for instance). It is meaningless to give CPU to something that waits for a lock, for instance, hence the reparation between signal wait and resource wait. Oct 24, 2022 at 13:46
  • @Sicilian-Najdorf In addition to what Tibor said, I added some more information to my answer for your last question.
    – J.D.
    Oct 24, 2022 at 14:10
2

SOS_SCHEDULER_YIELD wait time means that a task yielded its scheduler (which is what SQL OS calls a CPU) and is waiting to get scheduled again. After any wait a task needs a scheduler to continue processing. The portion of the wait when the task is ready to resume but does not have a scheduler is "signal wait time".

So SOS_SCHEDULER_YIELD Resource and Signal Wait Time + all other Signal Wait Time is the amount of time tasks spend waiting on a scheduler to resume processing.

So while SOS_SCHEDULER_YIELD waits don't necessarily indicate CPU pressure, significant SOS_SCHEDULER_YIELD and Signal wait time does. It's the time tasks spend ready to run, but unable to because no scheduler is available.

If a query has 10sec of SOS_SCHEDULER_YIELD wait time, it could have finished 10sec sooner if CPU had been available.

0

I think that most is covered by J.D.'s answer. I just like to comment on one thing

The second result in the picture shows signal waits at 7.39% - This doesn't correspond to what I am seeing in the first result. Am I missing something in my understanding?

That first result is likely from a query in Glenn Berry's diagnostic script. This should you signal wait vs resource wait for this particular wait type

The second result I guess aggregates waits for all wait types.

Also, Glenn query (The first one), ignores a bunch of irrelevant wait types (using NOT IN). Without that NOT IN, you will find a bunch of other waits being higher that the SOS_SCHEDULER_YIELD.

Above is of course (qualified) guesses, since you didn't post the queries that you are using.

2
  • Thank you This is the query I was using to get the overall signal wait vs resource waits: SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits], CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits] FROM sys.dm_os_wait_stats OPTION (RECOMPILE); I have updated this script to filter out irrelevant wait types and it yielded this result: %signal (CPU Waits) : 92.31 %resource waits: 7.69 Oct 24, 2022 at 14:05
  • That confirms my reasoning., thanks for the feedback. Oct 24, 2022 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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