Inside SQLOS – Scheduling

Question

Using SQL Server 2012 Enterprise Edition SP1 running on Windows Server 2008 R2 SP1 For each logical processor core on SQLOS has a scheduler assigned to it. Those scheduler can be viewed as status “VISIBLE ONLINE” in sys.dm_os_schedulers

Worker threads (tasks) can run on a scheduler and those threads (tasks ) can have various status like based on a waiting queue for e.g.

a) suspended in a waiting queue for resource to be available
b) runnable queue (mostly FIFO unless controlled by Resource Governor) or
c) currently running on a scheduler.

My question is say for e.g. a task (SELECT query in this case) first goes to a scheduler (let's call it scheduler A) but found that it has to wait until pages are brought into memory (IO Operation) it is now put into waiting queue in suspended state, once the IO is complete than that particular worker thread (tasks) is signaled and has been put into runnable queue in order in which it was arrived into that queue.

Does the task always have to go to scheduler A to finish its quantum or it can switch its context and can now run on any idle scheduler (let's call it scheduler B) with no runnable tasks? If there is a context switch what is its effect?

Answer 1

It has to wait on the scheduler A runnable queue, there is no switching of tasks to alternative schedulers. The time accumulated waiting for the scheduler to be available is referred to as a signal wait.

If a thread is ready to go, but the scheduler to which it’s assigned currently has another session running, then it will be placed in the “Runnable” queue. This means it’s in the queue to get on the processor. This is referred to as a signal wait (ref).

This is different and separate to the time accumulated while Suspended on the wait list.

The signal wait time is exposed by the signal_wait_time_ms column, and is solely CPU wait time. If a session is waiting for another resource to become available in order to proceed, such as a locked page, or if a running session needs to perform I/O, then it’s moved to the wait list. This is a resource wait and the waiting session’s status will be recorded as “Suspended.” (ref).

The relationship from sessions to schedulers maps out as:

Session -> Task -> Thread -> Scheduler -> Logical CPU

Once a thread is allocated to a scheduler it remains on that scheduler until it completes. It may move repeatedly between Running, Suspended and Runnable queues but will remain assigned to one scheduler.

A session may of course be comprised of multiple tasks and threads which are distributed across multiple schedulers as the result of a parallel execution plan.