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.

 

 

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'affinity mask', 65535;

RECONFIGURE;

GO

sp_configure ‘affinity64 mask’, 1;

RECONFIGURE;

GO

 

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