Tuning SQL Server 2005 on Windows Integrity Servers

Page 10 of 13
Use Connection Affinity to take further advantage of SQL Server NUMA features: Using connection
affinity, a SQL connection from the client is assigned affinity to a specific NUMA node. This assigns
data structures to that NUMA node, further enhancing the NUMA capabilities of SQL. When
connection affinity is enabled for all network connections, the requirement to use VIA for affinity no
longer applies. Moreover, multiple connection ports may be used on a single hardware network
adapter, further increasing the flexibility.
Soft NUMA allows the database administrator to configure pseudo-NUMA nodes that SQL Server
treats like hardware nodes. You can configure Soft NUMA nodes down to 1 processor, allowing fine
control of connection affinity and workload distribution. In addition, smaller Integrity servers without
hardware NUMA capabilities can still run SQL employing Soft NUMA. These machines do not
employ the NUMA concept of local and remote memory access, but they do allow the SQL
administrator to balance the workload at the level of a single processor.
If SQL2005 is run with no Soft NUMA nodes configured, then the hardware NUMA configuration is
used. The NUMA configuration, hard or soft, is displayed at startup time to the SQL Log.
To use these features:
– Use regedit to configure the Soft NUMA nodes and port listen strings
– Restart SQL
– Set clients to use ports configured above
Example
Create 4 Soft NUMA nodes, each of which has 2 CPUs.
Run regedit and add entries for the following format:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\90\NodeConfiguration]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\90\NodeConfiguration\Node0]
"CPUMask"=dword:00000003
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\90\NodeConfiguration\Node1]
"CPUMask"=dword:0000000C
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\90\NodeConfiguration\Node2]
"CPUMask"=dword:00000030
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\90\NodeConfiguration\Node3]
"CPUMask"=dword:000000C0
The CPUMask value is a bitmask of CPUs relative to the system. So Node0 above includes CPUs 0
and 1, Node1 includes CPUs 2 and 3, and so on. (Note that there is not a limit of 4 NUMA nodes;
this is just an example).
Note: Although SQL allows you to configure Soft NUMA nodes that cross hardware NUMA node
boundaries, this is not recommended because it results in excessive remote memory accesses.