SQL NUMA Node IDs reported in SQL Errorlog may not match hardware NUMA node IDs

Symptoms

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.

Cause

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:
alternate text

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.

alternate text
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.

More Information

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.

Additional information:


Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×