How to: Configure SQL Server to Use Soft-NUMA

Artikkeli
12/03/2008

New: 5 December 2005

To configure SQL Server to use software non-uniform memory access (soft-NUMA), you must edit the registry to add a node configuration affinity mask. The soft-NUMA mask can be stated as a binary or DWORD (hexadecimal or decimal) registry entry. You must restart the Database Engine to configure soft-NUMA. Before configuring software NUMA, see Understanding Non-uniform Memory Access and How SQL Server 2005 Supports NUMA.

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.

In this 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 1 through 4. A second instance of the Database Engine is installed and configured to use CPUs 5 through 8. The example can be visually represented as:

CPUs 1 2 3 4 5 6 7 8

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 two lazy writer threads, 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.

Set the CPU affinity mask

Run the following statement on instance A to configure it to use CPUs 1, 2, 3, and 4 by setting the CPU affinity mask:
```
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 15;
RECONFIGURE;
GO
```
Run the following statement on instance B to configure it to use CPUs 5, 6, 7, and 8 by setting the CPU affinity mask:
```
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 240;
RECONFIGURE;
GO
```

Note

The affinity mask value in the example applies to a computer with a 32-bit operating system.

Map soft-NUMA nodes to CPUs

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

Key	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

Jaa

How to: Configure SQL Server to Use Soft-NUMA

Set the CPU affinity mask

Map soft-NUMA nodes to CPUs

See Also

Tasks

Concepts

Help and Information

Lisäresursseja