Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

↑ Back to the top


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:


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.

↑ Back to the top


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:

↑ Back to the top


Keywords: kb

↑ Back to the top

Article Info
Article ID : 2632388
Revision : 1
Created on : 1/7/2017
Published on : 11/1/2011
Exists online : False
Views : 207