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

which connects to the second NUMA node; and all other workloads are afi nitized to port z, which
is set to connect to the third NUMA node.
CPU Nodes
A CPU node is a logical collection of CPUs that share some common resource, such as a cache or
memory. CPU nodes live below memory nodes in the SQLOS object hierarchy.
Whereas a memory node may have one or more CPU nodes associated with it, a CPU node can be
associated with only a single memory node. However, in practice, nearly all coni gurations have a
1:1 relationship between memory nodes and CPU nodes.
CPU nodes can be seen in the DMV sys . dm _ os _ nodes . Use the following query to return select col-
umns from this DMV:
select node_id, node_state_desc, memory_node_id, cpu_affinity_mask
from sys.dm_os_nodes
The results from this query, when run on a single-CPU system are as follows:
NODE_ID NODE_STATE_DESC MEMORY_NODE_ID CPU_AFFINITY_MASK
0 ONLINE 0 1
32 ONLINE DAC 0 0
The results from the previous query, when run on a 96-processor NUMA system, comprising four
nodes of four sockets, each socket with six cores, totaling 24 cores per NUMA node, and 96 cores
across the whole server, are as follows:
NODE_ID NODE_STATE_DESC MEMORY_NODE_ID CPU_AFFINITY_MASK
0 ONLINE 1 16777215
1 ONLINE 0 281474959933440
2 ONLINE 2 16777215
3 ONLINE 3 281474959933440
64 ONLINE DAC 0 16777215
NOTE The hex values for the cpu _ affinity _ mask values in this table are as
follows:
16777215 = 0x00FFFFFF
281474959933440 = 0x0F000001000000FFFFFF0000
This indicates which processor cores each CPU node can use.
Processor Ai nity
CPU afi nity is a way to force a workload to use specii c CPUs. It's another way that you can affect
scheduling and SQL Server SQLOS coni guration.
CPU afi nity can be managed at several levels. Outside SQL Server, you can use the operating
system's CPU afi nity settings to restrict the CPUs that SQL Server as a process can use. Within SQL