SQL NUMA Node IDs reported in SQL Errorlog may not match 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:


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.