When reviewing SQL error log files, the node IDs reported by SQL Server during the startup sequence may not match the hardware NUMA node IDs.
This behavior is by design.
The SQLOS has two types of nodes: Memory node and CPU node. The Memory node maps 1:1 to hardware, while the CPU node is a logical construct within SQL OS. The message printed to the Errorlog is at the CPU node level and provides logical IDs. Although in most cases, these logical IDs map 1:1 to hardware NUMA IDs, it may not always be true.
Each time the instance of SQL Server is started in a NUMA environment, the SQL error log contains informational messages describing the NUMA configuration. The format of the error message is as follows:
ErrorFormat: Node configuration: node %ld: CPU mask: 0x%0*I64x:%u Active CPU mask: 0x%0*I64x:%u.
This message provides a description of the NUMA configuration for this computer.
The following is an explanation of each of the above values:
Node configuration: Node id represents the SQL OS node id (cpu node) assigned internally by SQL
CPU Mask: The CPU's that form the above node
Active CPU mask: List of CPUs that are currently active in the above set of CPU's within the node.
For example if you have a database instance whose affinity is set to CPU NUMA node 2, SQL server error log may have an entry similar to the following:
In the above example, the CPU node id in SQL OS matches with the hardware NUMA node (2). After a SQL Server restart, you might see the following entries that shows a different CPU node id assignment.
If you take a closer look, you will see that even though node IDs are different, they relate to the same set of processors. It is important to note that this only happens at the CPU node layer (logical). It does not affect memory nodes (hardware) nor ALTER SERVER CONFIGURATION (hardware)
You can confirm this by querying: sys.dm_os_memory_nodes and sys.dm_os_nodes.
When starting a system, Windows allocates memory for the operating system from hardware NODE 0. Accordingly, hardware NODE 0 has less local memory available for other applications than the other nodes. This problem is accentuated when there is a large system file cache. When SQL Server starts on a computer with more than one NUMA node, it tries to start on a NUMA node other than NODE 0 so that its global structures can be allocated on the local memory. In the current versions of SQL Server, CPU node 0 is swapped with the first online node if any, that is available, to make CPU node 0 online.