SOS_Scheduler_yield – Wait types of SQL Server

Hello there,

This is my first blog for SQL server geeks and here I am starting with a series of wait types.

In this series we will unfold different wait types, their meaning and what could be the root cause and possible solutions for it

So before starting let me give this forward /note/ warning 🙂

Methodologies discussed in my blogs are pertained to my environment and this can’t hold true for all the people out there, so please don’t conclude your problem with the following

Having done with formalities, let dive in to the real content

So what is wait type and why I need to care about it

Wait types are different types on which SQL Server waits before completing the execution of process.

We need to care about wait types because they are the main tools for troubleshooting. Frankly speaking these are the starting point at which we need to troubleshoot any performance problem.  A fair idea on different wait types help a developer/dba to isolate the problem quickly.

With this blog we will help you get this knowledge.

Today we will discuss about the most common wait type we see in SQL Server i.e., SOS_SCHEDULER_YIELD.

If you are working on SQL Server for some time I bet you would have observed this word quite many number of times , so why this is common in all environments?

To understand this, we actually need to understand the life cycle of a process

Different states of process

Select status from sys.sysprocesses where spid=53-- Any spid Number

If you execute above query continuously you would see following states of the process

1. Running
2. Runnable
3. Suspended
4. Sleeping

There are others to such as dormant, back ground but we will limit to most prevalent ones.

1. Running

A process will be in running state, if the spid is actually running at CPU or in other words when the process consumes cpu cycles. During this state obviously process will not wait for anything as it is currently executing

2. Runnable

If the process has acquired all required resources but waiting for signal ( waiting for its turn to get to CPU) then the process would be in runnable state. Ideally if all my processes has adequate resources and I have only one processor all the processes would be in runnable state waiting for their turn to get to cpu. SQL OS follows a queue data structure to get this done (FIFO)

3. Suspended

If the process is lagging any of the resources like PAGE, LOCK, LATCH etc.. Then processor sets the appropriate wait type and keeps it in suspended state and once it acquires its resource it joins the queue and will wait for the signal.

4. Sleeping

When the process completes its execution, it will be in sleeping state. No wait type will be associated at this state too.

If you observe above points we haven’t discussed about wait type nature of a process during runnable state and the reason is the wait type for a process which is in runnable is SOS_SCHEDULER_YIELD

Why this wait type during that state?

The limit for any process to be in running state is 4 MS (approximately) and after that process will yield itself and will give chance to another process to be in running state. After 4 ms if the process has any deficit of resources it goes to suspended state but otherwise it goes back to runnable queue waiting for signal.

So, SOS_SCHEDULER_YIELD is common to be there in every system as this is not a problem but behavior of SQL Server.

So, when should I bother with this wait type?

You should bother about this wait type during following two situations

• If you have so many processes with this wait type and wait times are low then that situation usually mean there are many CPU bound queries.
• If you have few processes with these wait types but having high wait times then that could be non-yielding problem.

We will look at how to debug above scenarios and also what different areas this wait type could occur in next blogpost, stay tuned

Happy Learning guys,
Aditya Badramraju

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Add me to your network