Soft-NUMA (SQL Server)

Soft-NUMA (SQL Server)

 

Updated: July 11, 2016

Applies To: SQL Server 2016

Modern processors have multiple to many 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 SP2, software-based NUMA (soft-NUMA) required you to edit the registry to add a node configuration affinity mask and was configured per computer rather than per instance. With SQL Server 2014 SP2 and SQL Server 2016, soft-NUMA is configured automatically at the database-instance level when the SQL Server service starts.

System_CAPS_ICON_note.jpg Note


Hot-add processors are not supported by soft-NUMA.

With SQL Server 2016, whenever the database engine server detects more than 8 logical processors at startup, soft-NUMA nodes are created automatically by default. Physical and hyper-threaded processor cores are not differentiated when counting logical processors. When the number of logical processors detected more than 8 per socket, the database engine service will create soft-NUMA nodes that ideally contain 8 cores, but can go down to 5 or up to 9 logical processors per node. The size of the hardware node can be limited by a CPU affinity mask. See
ALTER SERVER CONFIGURATION (Transact-SQL). The number of NUMA nodes will never exceed 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 will see in the SQL Server error log when SQL Server detects hardware NUMA nodes with greater than 8 logical processors.

Soft-NUMA Error Log

To can manually configure
SQL Server to use soft-NUMA by disabling automatic soft_NUMA and editing 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.) After modifying the registry, you must restart the
Database Engine for the soft-NUMA configuration to take effect.

System_CAPS_ICON_tip.jpg Tip


CPUs are numbered starting with 0.

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 following example. A computer with eight CPUs 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:

CPUs 0 1 2 3 4 5 6 7

Soft-NUMA <-N0--><-N1-><----N2---->

SQL Server <instance A ><instance B>

Instance A, which experiences significant I/O, now has two I/O threads and
one l
azy writer thread, while instance B, which performs processor-intensive operations, has only one I/O thread and one lazy writer thread. Differing amounts of memory can be assigned to the instances, but unlike hardware NUMA, they both receive memory from the same operating system memory block and there is no memory-to-processor affinity.

The lazy
writer thread is tied to the SQL OS view of the physical NUMA memory nodes. Therefore, whatever the hardware presents as physical NUMA nodes will equate to the number of lazy writer threads that are created. For more information, see
How It Works: Soft NUMA, I/O Completion Thread, Lazy Writer Workers and Memory Nodes.

System_CAPS_ICON_note.jpg Note


The
Soft-NUMA registry keys are not copied when you upgrade an instance of
SQL Server.

Set the CPU affinity mask

Run the following statement on instance A to configure it to use CPUs 0, 1, 2, and 3 by setting the CPU affinity mask:

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY CPU=0 TO 3;  

Run the following statement on instance B to configure it to use CPUs 4, 5, 6, and 7 by setting the CPU affinity mask:

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY CPU=4 TO 7;  

Map soft-NUMA nodes to CPUs

Using the Registry Editor program (regedit.exe), add the following registry keys to map soft-NUMA node 0 to CPUs 0 and 1, soft-NUMA node 1 to CPUs 2 and 3, and soft-NUMA node 2 to CPUs 4. 5, 6, and 7.

System_CAPS_ICON_tip.jpg Tip


To specify CPUs 60 through 63, use a QWORD value of
F000000000000000 or a BINARY value of 1111000000000000000000000000000000000000000000000000000000000000.

In the following example, assume you have a DL580 G9 server, with 18 cores per socket (in 4 sockets), and each socket is in its own K-group. A soft-numa configuration that you might create would look something like following. (6 cores per Node, 3 nodes per group, 4 groups).

Example for a
                                        SQL Server 2016 server with multiple K-Groups
TypeValue nameValue data
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node0DWORDCPUMask0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node0DWORDGroup0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node1DWORDCPUMask0x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node1DWORDGroup0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node2DWORDCPUMask0x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node2DWORDGroup0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node3DWORDCPUMask0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node3DWORDGroup1
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node4DWORDCPUMask0x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node4DWORDGroup1
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node5DWORDCPUMask0x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node5DWORDGroup1
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node6DWORDCPUMask0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node6DWORDGroup2
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node7DWORDCPUMask0x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node7DWORDGroup2
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node8DWORDCPUMask0x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node8DWORDGroup2
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node9DWORDCPUMask0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node9DWORDGroup3
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node10DWORDCPUMask0x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node10DWORDGroup3
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node11DWORDCPUMask0x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node11DWORDGroup3

You can use the following DMVs to view the current state and configuration of soft_NUMA.

System_CAPS_ICON_note.jpg Note


While you can view the running value for automatic soft-NUMA using sp_configure (Transact-SQL), you cannot change its value using sp_configure. You must use the ALTER SERVER CONFIGURATION (Transact-SQL) statement with the SET SOFTNUMA argument.

Map TCP IP Ports to NUMA Nodes (SQL Server)
affinity mask Server Configuration Option
ALTER SERVER CONFIGURATION (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft