Understanding of Numa Node in SQL Server
Modern processors have multiple cores per socket. Each socket is represented, usually, as a single NUMA node. The SQL Server database engine partitions various internal structures and partitions service threads per NUMA node. With processors containing 10 or more cores per socket, using software NUMA to split hardware NUMA nodes generally increases scalability and performance. Prior to SQL Server 2014 (12.x) SP2, software-based NUMA (soft-NUMA) required you to edit the registry to add a node configuration affinity mask, and was configured at the host level, rather than per instance. Starting with SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x), soft-NUMA is configured automatically at the database-instance level when the SQL Server Database Engine service starts.
Modern processors have multiple cores per socket. Each socket is represented, usually, as a single NUMA node. The SQL Server database engine partitions various internal structures and partitions service threads per NUMA node. With processors containing 10 or more cores per socket, using software NUMA to split hardware NUMA nodes generally increases scalability and performance. Prior to SQL Server 2014 (12.x) SP2, software-based NUMA (soft-NUMA) required you to edit the registry to add a node configuration affinity mask, and was configured at the host level, rather than per instance. Starting with SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x), soft-NUMA is configured automatically at the database-instance level when the SQL Server Database Engine service starts.
Automatic Soft-NUMA
With SQL Server 2016 (13.x), whenever the SQL Server Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. Hyper-threaded processor cores are not differentiated when counting physical cores in a node. When the detected number of physical cores is more than eight per socket, the SQL Server Database Engine creates soft-NUMA nodes that ideally contain eight cores, but can go down to five or up to nine logical cores per node. The size of the hardware node can be limited by a CPU affinity mask. The number of NUMA nodes never exceeds the maximum number of supported NUMA nodes.
You can disable or re-enable soft-NUMA using the ALTER SERVER CONFIGURATION (Transact-SQL)statement with the
SET SOFTNUMA argument. Changing the value of this setting requires a restart of the database engine to take effect.
The figure below shows the type of information regarding soft-NUMA that you see in the SQL Server error log, when SQL Server detects hardware NUMA nodes with greater than eight physical cores per each node or socket.
2016-11-14 13:39:43.17 Server SQL Server detected 2 sockets with 12 cores per socket and 24 logical processors per socket, 48 total logical processors; using 48 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2016-11-14 13:39:43.35 Server Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.
2016-11-14 13:39:43.63 Server Node configuration: node 0: CPU mask: 0x0000000000555555:0 Active CPU mask: 0x0000000000555555:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2016-11-14 13:39:43.63 Server Node configuration: node 1: CPU mask: 0x0000000000aaaaaa:0 Active CPU mask: 0x0000000000aaaaaa:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2016-11-14 13:39:43.63 Server Node configuration: node 2: CPU mask: 0x0000555555000000:0 Active CPU mask: 0x0000555555000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2016-11-14 13:39:43.63 Server Node configuration: node 3: CPU mask: 0x0000aaaaaa000000:0 Active CPU mask: 0x0000aaaaaa000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
Manual Soft-NUMA
To manually configure SQL Server to use soft-NUMA, disable automatic soft-NUMA, and edit the registry to add a node configuration affinity mask. When using this method, the soft-NUMA mask can be stated as a binary, DWORD (hexadecimal or decimal), or QWORD (hexadecimal or decimal) registry entry. To configure more than the first 32 CPUs use QWORD or BINARY registry values (QWORD values cannot be used prior to SQL Server 2012 (11.x)). After modifying the registry, you must restart the Database Engine for the soft-NUMA configuration to take effect.
Tip
CPUs are numbered starting with 0.
Warning
Incorrectly editing the registry can severely damage your system. Before making changes to the registry, we recommend that you back up any valued data on the computer.
Consider the example of a computer with eight CPUs, that does not have hardware NUMA. Three soft-NUMA nodes are configured.
Database Engine instance A is configured to use CPUs 0 through 3. A second instance of the Database Engine is installed and configured to use CPUs 4 through 7. The example can be visually represented as:
Database Engine instance A is configured to use CPUs 0 through 3. A second instance of the Database Engine is installed and configured to use CPUs 4 through 7. The example can be visually represented as:
CPUs 0 1 2 3 4 5 6 7Soft-NUMA <-N0--><-N1-><----N2---->SQL Server <instance A ><instance B>
Instance A, which experiences significant I/O, now has two I/O
