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:
- “It must be spinlocks that are the problem” – no, spinlocks are not tracked by wait types
- “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
- “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).