Skip to content

soft-numa-sql-server.md

Latest commit

 

History

History
139 lines (107 loc) · 12.8 KB

soft-numa-sql-server.md

File metadata and controls

139 lines (107 loc) · 12.8 KB
title Soft-NUMA (SQL Server) | Microsoft Docs
ms.custom
ms.date 02/13/2018
ms.prod sql
ms.prod_service high-availability
ms.reviewer
ms.technology configuration
ms.topic conceptual
f1_keywords
NUMA
soft-NUMA
helpviewer_keywords
NUMA
non-uniform memory access
soft-NUMA
ms.assetid 1af22188-e08b-4c80-a27e-4ae6ed9ff969
author CarlRabeler
ms.author carlrab

Soft-NUMA (SQL Server)

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]

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 [!INCLUDEssSQL14] 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 [!INCLUDEssSQL14] SP2 and [!INCLUDEssSQL15], soft-NUMA is configured automatically at the database-instance level when the [!INCLUDEssDEnoversion] service starts.

Note

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

Automatic Soft-NUMA

With [!INCLUDEssSQL15], whenever the [!INCLUDEssDEnoversion] 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 [!INCLUDEssDEnoversion] 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 [!INCLUDEssNoVersion] 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.

Note

Starting with [!INCLUDEssSQL14] SP2, use trace flag 8079 to allow [!INCLUDEssNoVersion] to use Automatic Soft-NUMA. Starting with [!INCLUDEssSQL15] this behavior is controlled by the engine and trace flag 8079 has no effect. For more information, see DBCC TRACEON - Trace Flags.

Manual Soft-NUMA

To manually configure [!INCLUDEssNoVersion] 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 [!INCLUDEssSQL11]). After modifying the registry, you must restart the [!INCLUDEssDE] for the soft-NUMA configuration to take effect.

Tip

CPUs are numbered starting with 0.

Warning

[!INCLUDEssNoteRegistry]

Consider the example of a computer with eight CPUs, that does not have hardware NUMA. Three soft-NUMA nodes are configured.
[!INCLUDEssDE] instance A is configured to use CPUs 0 through 3. A second instance of the [!INCLUDEssDE] 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 lazy writer thread. 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 SQLOS view of the physical NUMA memory nodes. Therefore, whatever the hardware presents as the number of physical NUMA nodes, this will be 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.

Note

The Soft-NUMA registry keys are not copied when you upgrade an instance of [!INCLUDEssNoVersion].

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.

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 four sockets), and each socket is in its own K-group. A soft-NUMA configuration that you might create would look something like the following: six cores per Node, three nodes per group, four groups.

Example for a [!INCLUDEssSQL15] server with multiple K-Groups Type Value name Value data
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node0 DWORD CPUMask 0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node0 DWORD Group 0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node1 DWORD CPUMask 0x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node1 DWORD Group 0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node2 DWORD CPUMask 0x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node2 DWORD Group 0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node3 DWORD CPUMask 0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node3 DWORD Group 1
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node4 DWORD CPUMask 0x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node4 DWORD Group 1
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node5 DWORD CPUMask 0x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node5 DWORD Group 1
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node6 DWORD CPUMask 0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node6 DWORD Group 2
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node7 DWORD CPUMask 0x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node7 DWORD Group 2
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node8 DWORD CPUMask 0x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node8 DWORD Group 2
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node9 DWORD CPUMask 0x3F
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node9 DWORD Group 3
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node10 DWORD CPUMask 0x0fc0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node10 DWORD Group 3
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node11 DWORD CPUMask 0x3f000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\NodeConfiguration\Node11 DWORD Group 3

Metadata

You can use the following DMVs to view the current state and configuration of soft-NUMA.

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.

See Also

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