The unit of work for a SQL Server worker is a request, or a task, which you can
think of as being equivalent to a single
batch sent from the client to the server.
Once a request is received by SQL Server, it is bound to a
worker, and that worker processes the entire request before handling any other
request. This holds true even if the request is blocked for some reason, such
as while it waits for a lock or for I/O to complete. The particular worker does
not handle any new requests but waits until the blocking condition is resolved
and the request can be completed. Keep in mind that a session ID (SPID) is not
the same as a task. A SPID is a connection or channel over which requests can
be sent, but there is not always an active request on any particular SPID.
In SQL Server
2008, a SPID is not bound to a particular scheduler. Each SPID has a preferred
scheduler, which is the scheduler that most recently processed a request from
the SPID. The SPID is initially assigned to the scheduler with the lowest load.
(You can get some insight into the load on each scheduler by looking at the load_factor
column in the DMV sys.dm_os_schedulers.)
However, when subsequent requests are sent from the same SPID, if another scheduler has a load factor that is
less than a certain percentage of the average of the scheduler’s entire load factor, the new task
is given to the scheduler with the smallest load factor. There is a restriction
that all tasks for one SPID must be processed by schedulers on the same NUMA
node. The exception to this restriction is when a query is being executed as a
parallel query across multiple CPUs. The optimizer can decide to use more CPUs
that are available on the NUMA node processing the query, so other CPUs (and
other schedulers) can be used.
