Today’s subject is a very interesting subject and I got lots of people asking me about this after watching my Sleeping vs Suspended Process – SQL in Sixty Seconds #122. If you have not watched my previous SQL in the Sixty Seconds video on this topic, I strongly suggest that you first watch it before we continue on today’s topic of SQLOS Scheduler and the Process States.
The question which I received after watching my earlier video was as follows:
How do we map the SQLOS with various Process States?
I really usually discuss the topic during my Comprehensive Database Performance Health Check when we are discussing Performance Tuning, but let me try to answer this it here in as simple words as possible.
SQL OS Scheduler is a SQL Server’s own scheduler and it does not depend on the Windows OS for its processes scheduling. It kind of works on its own based on the available resources and algorithm programmed in it. It has three major components.
Before you read about the following three component’s I want you to think of your production system and also visualize how many threads and processes you are working on any one point. I commonly see SQL Server dealing with hundreds of processes at any single point of time. Let us now learn how all of this is managed in SQL Server.
Processor/CPU: This is a logical CPU (physical CPU) core that is actually processing all the threads. Traditionally, it processes one thread a time. A thread in this component is commonly called a RUNNING state for processes.
Ready Queue/Runnable Queue: Now as Processor is attending a single thread at a time, all the threads which are ready for CPU, needs to wait somewhere and they all wait their turn in the Ready Queue which is commonly known as a Runnable Queue. The usual logic which is followed here is First in First Out (commonly known as FIFO). However, this does not apply when the resource governor is in play, but that is a conversation about some other time. A thread in this component is commonly called a RUNNABLE state for processes.
Waiting Queue: Now when CPU gets to the thread, it is quite possible the thread may not have the necessary IO, in that situation, the thread has to be moved to another state called suspended and it is when it goes to Waiting Queue. It waits here as long as it has to wait for the necessary resources are available again. There is technically no limit on how long a thread can wait here unless you have configured execution timeout. A thread in this component is commonly called a SUSPENDED state for processes.
Once the necessary resource is available once again thread is moved to a runnable queue. I hope you find today’s blog post about SQLOS Scheduler informative. Do leave a comment if you have any further questions. You can always reach out to me on twitter here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
