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

Server's coni guration settings, you can specify that SQL Server should use only certain CPUs. This
is done using the afi nity mask and afi nity64 mask coni guration options. Changes to these two
options are applied dynamically, which means that schedulers on CPUs that are either enabled or
disabled while SQL is running will be affected immediately. Schedulers associated with CPUs that
are disabled will be drained and set to ofl ine. Schedulers associated with CPUs that are enabled will
be set to online, and will be available for scheduling workers and executing new tasks.
You can also set SQL Server I/O afi nity using the afi nity I/O mask option. This option enables you
to force any I/O-related activities to run only on a specii ed set of CPUs. Using connection afi nity as
described earlier in the section “Soft NUMA,” you can afi nitize network connections to a specii c
memory node.
Schedulers
The scheduler node is where the work of scheduling activity occurs. Scheduling occurs against tasks ,
which are the requests to do some work handled by the scheduler. One task may be the optimized
query plan that represents the T-SQL you want to execute; or, in the case of a batch with multiple
T-SQL statements, the task would represent a single optimized query from within the larger batch.
When SQL Server starts up, it creates one scheduler for each CPU that it i nds on the server, and
some additional schedulers to run other system tasks. If processor afi nity is set such that some
CPUs are not enabled for this instance, then the schedulers associated with those CPUs will be set to
a disabled state. This enables SQL Server to support dynamic afi nity settings.
While there is one scheduler per CPU, schedulers are not bound to a specii c CPU, except in the case
where CPU afi nity has been set.
Each scheduler is identii ed by its own unique scheduler_id. Values from 0-254 are reserved for
schedulers running user requests. Scheduler_id 255 is reserved for the scheduler for the dedicated
administrator connection (DAC). Schedulers with a scheduler_id > 255 are reserved for system use
and are typically assigned the same task.
The following code sample shows select columns from the DMV sys . dm _ os _ schedulers :
select parent_node_id, scheduler_id, cpu_id, status, scheduler_address
from sys.dm_os_schedulers
order by scheduler_id
The following results from the preceding query indicate that scheduler_id 0 is the only scheduler
with an id < 255, which implies that these results came from a single-core machine. You can also see
a scheduler with an ID of 255, which has a status of VISIBLE ONLINE (DAC) , indicating that this is
the scheduler for the DAC. Also shown are three additional schedulers with IDs > 255. These are the
schedulers reserved for system use.
PARENT_NODE_ID SCHEDULER_ID CPU_ID STATUS SCHEDULER_ADDRESS
0 0 0 VISIBLE ONLINE 0x00480040
32 255 0 VISIBLE ONLINE (DAC) 0x03792040
0 257 0 HIDDEN ONLINE 0x006A4040
0 258 0 HIDDEN ONLINE 0x64260040
0 259 0 HIDDEN ONLINE 0x642F0040