How to configure Soft-NUMA on a system with > 32 processors?
I recently answered this customer question and was looking forward to post the solution here. So here you go!
There is already a lot of information about how Soft NUMA works and how to configure Soft NUMA on your system. So without delving into the basics, I am jumping right ahead to the topic.
Soft Affinity Setting
SQL Server 2000 and above supports the affinity option for greater than 32 processors (33 to 64) only on 64-bit operating system.
If you are running SQL Server 2000 or above on a system with greater than 32 processors, then you can use sp_configure ‘affinity mask’ (32 bit mask) option for the first 32 CPUs and sp_configure ‘affinity64 mask’ (32 bit mask) option for the CPUs 33 to 64.
Ex: Consider a machine with 33 CPUs. Setting the ‘affinity mask’ to 0x0000ffff and ‘affinity64 mask’ to 0x1 hard affinitizes CPU 1 to 16 and CPU 33 to ONLINE.
|
Registry Settings
To map Soft NUMA nodes to greater than 32 CPUs, use a QWORD registry value to represent CPUs 1 to 64. Use the Registry Editor program (regedit.exe) to add the following registry keys to map soft-NUMA to CPUs as follows.
Ex: Mapping Soft NUMA nodes to CPUs as follows:
Node 0: CPUs 1 to 16
Node 1: CPUs 17 to 32
Node 2: CPUs 33 and 34
SQL Server 2005 |
Type |
Value name |
Value data |
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\Node0 |
QWORD |
CPUMask |
0x 0000 0000 0000 ffff |
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\Node1 |
QWORD |
CPUMask |
0x0000 0000 ffff 0000 |
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\NodeConfiguration\Node2 |
QWORD |
CPUMask |
0x0000 0002 0000 0000 |
SQL Server 2008 |
Type |
Value name |
Value data |
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node0 |
QWORD |
CPUMask |
0x 0000 0000 0000 ffff |
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node1 |
QWORD |
CPUMask |
0x0000 0000 ffff 0000 |
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node2 |
QWORD |
CPUMask |
0x0000 0003 0000 0000 |
Rest of the details about using Soft NUMA and mapping the registry key remains the same.
If the registry is having faulty settings like overlapping NUMA node masks to CPU mapping or Soft NUMA mapping crossing hardware NUMA, then SQL Server ignores the Soft NUMA configuration.
Good news is that the new version of SQL Server (after SQL Server 2008) will be supporting greater than 64 logical processors and will be using a more convenient way for setting affinity!
-Harshitha Amit
SQLOS Test Team