Share via


Configure SQL Server to Use Soft-NUMA (SQL Server)

Non-uniform memory access (NUMA) is detected and used automatically. If you want to simulate NUMA of large SMB machines, or if you want to subdivide NUMA machines for better locality, then you can use software NUMA (soft-NUMA). To configure SQL Server to use soft-NUMA, you must edit the registry to add a node configuration affinity mask. 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.) You must restart the Database Engine to configure soft-NUMA.

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 lazy 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.

Note

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

Set the CPU affinity mask

  1. 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;
    
  2. 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.

    SQL Server 2012

    Type

    Value name

    Value data

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\NodeConfiguration\Node0

    DWORD

    CPUMask

    0x03

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\NodeConfiguration\Node0

    DWORD

    Group

    0

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\NodeConfiguration\Node1

    DWORD

    CPUMask

    0x0c

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\NodeConfiguration\Node1

    DWORD

    Group

    0

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\NodeConfiguration\Node2

    DWORD

    CPUMask

    0xf0

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\NodeConfiguration\Node2

    DWORD

    Group

    0

    Tip

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

    SQL Server 2008 R2

    Type

    Value name

    Value data

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node0

    DWORD

    CPUMask

    0x03

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node0

    DWORD

    Group

    0

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node1

    DWORD

    CPUMask

    0x0c

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node1

    DWORD

    Group

    0

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node2

    DWORD

    CPUMask

    0xf0

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node2

    DWORD

    Group

    0

    SQL Server 2008

    Type

    Value name

    Value data

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node0

    DWORD

    CPUMask

    0x03

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node1

    DWORD

    CPUMask

    0x0c

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node2

    DWORD

    CPUMask

    0xf0

    SQL Server 2005

    Type

    Value name

    Value data

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\Node0

    DWORD

    CPUMask

    0x03

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\Node1

    DWORD

    CPUMask

    0x0c

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\Node2

    DWORD

    CPUMask

    0xf0

See Also

Reference

ALTER SERVER CONFIGURATION (Transact-SQL)

Concepts

Map TCP/IP Ports to NUMA Nodes (SQL Server)

affinity mask Server Configuration Option