How to set Soft-NUMA for SQL Server 2008 R2

This is probably one of the murky topics of SQL Server. Add to this equation, hardware NUMA and the CPU Mask values can be a tough one to determine.

Books Online has an article on how to configure Soft-NUMA for a SQL Server 2008 R2 instance. Rob Dorr has an excellent blog post which explains how Soft-NUMA works and it’s implications on I/O Completion, Lazy Writer and the SQL OS Memory Nodes on the server. If you have hardware NUMA enabled on the machine, then you should be able to view the configuration from the DMV: sys.dm_os_nodes. A part of the output is mentioned below (Taken from a box with a 2-node hardware NUMA with 8 logical processors).

From the above table, it is clear that I have two NUMA nodes which have two SQL OS memory nodes aligned to it. Since I have not set Soft-NUMA on the server, the above node configuration is due to hardware NUMA. I have CPUs (0,1,2,3) aligned to NUMA node 0 and CPUs(4,5,6,7) aligned to NUMA node 1.

0x00000000000000F0 – 11110000

0x000000000000000F – 00001111

Processors (0, 1, 2, 3) [0xf] have the following relationships:

            The specified logical processors are part of the same NUMA node.

            The node number is: 0

Processors (4, 5, 6, 7) [0xf0] have the following relationships:

            The specified logical processors are part of the same NUMA node.

            The node number is: 1

As per the BOL article, if I were to create 4 Soft-NUMA nodes on my instance, I would need to add the registry keys as follows (for a box with 8 logical CPUs):

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration]

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

"CPUMask"=dword:00000003

"Group"=dword:00000000

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

"CPUMask"=dword:0000000c

"Group"=dword:00000000

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

"CPUMask"=dword:00000030

"Group"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node3]

"CPUMask"=dword:000000c0

"Group"=dword:00000000

Based on the above registry configuration, I now have 4 Soft-NUMA nodes with following processors mappings:

Soft-NUMA node 0: Processors: 0, 1

Soft-NUMA node 1: Processors: 2, 3

Soft-NUMA node 2: Processors: 4, 5

Soft-NUMA node 3: Processors: 6, 7

I have set the Processor Affinity on my instance to use logical CPUs: 0, 1, 4, 5. When I start the SQL server instance for the Soft-NUMA settings to take effect, I should see the following in the SQL Errorlog:

Processor affinity turned on: node 0, processor mask 0x0000000000000030.

Processor affinity turned on: node 1, processor mask 0x0000000000000000.

Processor affinity turned on: node 2, processor mask 0x0000000000000003.

Processor affinity turned on: node 3, processor mask 0x0000000000000000.

Node configuration: node 0: CPU mask: 0x0000000000000030:0 Active CPU mask: 0x0000000000000030:0.

Node configuration: node 1: CPU mask: 0x000000000000000c:0 Active CPU mask: 0x0000000000000000:0.

Node configuration: node 2: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0.

Node configuration: node 3: CPU mask: 0x00000000000000c0:0 Active CPU mask: 0x0000000000000000:0.

Notice that the Processor Affinity set for the SQL instance is reported for soft-NUMA nodes 0 and 2 as those are the two soft-NUMA nodes that have the processors that I have configured this particular instance to use CPUs – 0,1, 4, 5. Next the node configuration is now reported as 4 nodes. This can be verified using the sys.dm_os_nodes DMV also. Note that the Node configuration reports a number 0 after the colon (0x0000000000000000:0) for each CPU mask. This number signifies the Processor Group number.

Sys.dm_os_nodes output

Sys.dm_os_schedulers output

If you notice the above output, you will see that there are only 2 memory nodes as there are 2 hardware NUMA nodes on the box. However, there are 4 Soft-NUMA nodes as per the registry values created for the Soft-NUMA configuration. The sys.dm_os_schedulers tells you the same story and also shows the scheduler mapping with each Soft-NUMA node.

Now there are two gotchas for configuring Soft-NUMA for SQL Server 2008 R2 instances when your box has Hardware NUMA.

First Gotcha: Incorrect CPUMask value

The CPU mask in the registry settings cannot mix and match logical CPUs that belong to two different hardware NUMA nodes.

Eg. A CPUMask value of 0x11 (CPUs 1, 5) would not be valid for Soft-NUMA node 0 as the logical CPUs provided for the node spans hardware NUMA nodes 0 and 1.

If you provide incorrect CPU mask values in the registry settings for the Soft-NUMA configuration, then this will not be honored and your Errorlog will report the hardware NUMA setting on the box.

Second Gotcha: Incorrect Group value (applicable for SQL Server 2008 R2 only)

You will notice that the BOL article for configuring Soft-NUMA mentions that a Group DWORD value for each Soft-NUMA node. The Group value will always be 0 for all systems with logical processors below 64. The concept of processor groups was introduced for Windows Server 2008 R2 and Windows 7. Documentation on the same mentions the following:

Systems with multiple processors or systems with processors that have multiple cores furnish the operating system with multiple logical processors. A logical processor is one logical computing engine from the perspective of the operating system, application or driver. In effect, a logical processor is a thread.

Support for systems that have more than 64 logical processors is based on the concept of a processor group. A processor group is a static set of up to 64 logical processors that is treated as a single scheduling entity.

When the system starts, the operating system creates processor groups and assigns logical processors to the groups. A system can have up to four groups, numbered 0 to 3. Systems with fewer than 64 logical processors always have a single group, Group 0. The operating system minimizes the number of groups in a system. For example, a system with 128 logical processors would have two processor groups, not four groups with 32 logical processors in each group.

So if you set your Group value incorrectly, then your SQL Server 2008 R2 instance will again not honor the Soft-NUMA settings.

The processor group can be determined from the processor_group column in the sys.dm_os_nodes DMV output.

The above is applicable for SMP systems as well. Hope this makes things easier while trying to configure Soft-NUMA for SQL Server instances.

Additional References:

Slava Ok’s blog post on Soft NUMA for SQL Server 2005

https://blogs.msdn.com/b/slavao/archive/2005/08/18/453354.aspx

Regards,
Amit Banerjee
SEE, Microsoft SQL support