Inside the Database Engine, the SQLOS uses the Task Execution Model to schedule worker threads. Each task or statement is assigned to a worker thread. Worker threads are used to access a CPU. This is called non-preemptive or cooperative thread scheduling.

Cooperative Thread Scheduling

Cooperative thread scheduling is where a thread will yield access to the CPU. This allows equal access to the processor for every task. Thread yielding happens in two scenarios. Either the thread has reached its quantum limit of 4 milliseconds or is waiting on a resource other than then CPU.

The Runnable List Queue and Running Tasks

Let’s start with a task that needs to run a simple SELECT statement. When that query is executed, it is placed in the Runnable List queue with a status of Runnable. This is a queue of all threads that are waiting for the CPU. This queue is organized in a First In, First Out (FIFO) order. The first task that was placed in the queue will have priority to access the CPU.

Once the CPU is available it will send a signal to the Runnable List queue. The task assigned to the thread is given access to process its task. The status of this task will change to Running. If it completes within the 4ms quantum then the task is complete. If a task takes longer than 4ms it returns to the Runnable List to wait for its next turn to access the processor.

The Waiter List Queue

This is true unless the task needs to wait for a resource other than the CPU. For example, a table the task is trying to access has been locked by another transaction. In this scenario, the task is placed in the Waiter List queue, in no specific order, and given a status of Suspended. The task will remain in this state until the requested resource is available. Once the resource is available, the task will be placed back in the Runnable List queue to again wait its turn for the CPU.

There are many reasons why a task would be placed in the Waiter List queue. Aaron Bertrand has a list of the Top Ten Wait Types and how you should handle them. If you would like to see most of the wait types, I would suggest the SQL Skills Wait Types Library maintained by Paul Randal and the SQL Skills staff. For further reading, check out the SQL Performance Tuning: Using Wait Statistics Guide (Written by: Erin Stellato and Jonathan Kehayias).