Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 146

Interesting columns in this DMV include the following:
➤

Scheduler_address — Address of the scheduler with which the thread is associated

➤

Worker_address — Address of the worker currently associated with the thread

➤

Kernel_time — Amount of kernel time that the thread has used since it was started

➤

Usermode_time — Amount of user time that the thread has used since it was started

Scheduling
Now that you have seen all the objects that SQLOS uses to manage scheduling, and understand
how to examine what's going on within these structures, it's time to look at how SQL OS actually
schedules work.

One of the main things to understand about scheduling within SQL Server is that it uses a non-
preemptive scheduling model, unless the task being run is not SQL Server code. In that case, SQL
Server marks the task to indicate that it needs to be scheduled preemptively. An example of code
that might be marked to be scheduled preemptively would be any code that wasn't written by SQL
Server that is run inside the SQL Server process, so this would apply to any CLR code.

PREEMPTIVE VS. NONPREEMPTIVE SCHEDULING

With preemptive scheduling, the scheduling code manages how long the code can
run before interrupting it, giving some other task a chance to run.
The advantage of preemptive scheduling is that the developer doesn't need to think
about yielding; the scheduler takes care of it. The disadvantage is that the code can
be interrupted and prevented from running at any arbitrary point, which may result
in the task running more slowly than possible. In addition, providing an environ-
ment that offers preemptive scheduling features requires a lot of work.

With nonpreemptive scheduling, the code that's being run is written to yield control
at key points. At these yield points, the scheduler can determine whether a different
task should be run.
The advantage of nonpreemptive scheduling is that the code running can best deter-
mine when it should be interrupted. The disadvantage is that if the developer doesn't
yield at the appropriate points, then the task may run for an excessive amount of
time, retaining control of a CPU when it's waiting. In this case, the task blocks other
tasks from running, wasting CPU resources.

SQL Server begins to schedule a task when a new request is received, after the Query Optimizer
has completed its work to i nd the best plan. A task object is created for this user request, and the
scheduling starts from there.

The newly created task object has to be associated with a free worker in order to actually do
anything. When the worker is associated with the new task, the worker's status is set to init . When