Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Just to clarify one point, Schedulers aren't bound by default to individual cpu's. By default Scheduler "A" could run on any cpu in the same NUMA Node. If you use Resource Governor to affinitize, then SQLOS does bind schedulers to cpus. (This is per Bob Ward's SQLOS session at PASS 2012) –  StrayCatDBA Jan 30 '13 at 7:45
    
Awesome, Thanks. –  SQL Learner Jan 31 '13 at 19:52
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
Does session means spid? –  SQL Learner Jan 26 '13 at 15:08
2  
@SQLLearner yes, session and spid are synonymous in this context. You'll notice that all of the modern DMVs show session_id vs. the old nomenclature you'll find in the output of things like sp_who2 and sysprocesses. But they are referring to the same thing. –  Aaron Bertrand Jan 26 '13 at 15:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.