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
There are others to such as dormant, back ground but we will limit to most prevalent ones.
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
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)
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.
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.