Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980

Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980
17
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 database administrators 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 x86 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 down to the level of a single processor.
If SQL Server is run with no Soft NUMA nodes configured, then the hardware NUMA configuration is used. The NUMA
configuration, hard or soft, is written at startup to the SQL log.
To use these features, follow these steps:
1. Use regedit to configure the Soft NUMA nodes and port listen strings.
2. Restart SQL.
3. Set clients to use ports configured above.
It is also recommended that you install the HP System Providers v 9.0 or later on the system and use the Optimize Logical
Processor Configuration button in the HP System Management Homepage to preset your logical processor and kernel
groups (for nodes/group optimization).
A Configuration example: How to create 4 Soft NUMA nodes with 2 CPUs each
This example is for systems running SQL Server 2008 R2, and specifies the Group parameter, which applies only to systems
with >64 LPs. This example should be ignored for SQL Server 2008, or if the executing system is equipped with ≤64 LPs.
First, run regedit and add the following entries to the system registry:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration
[HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration
\Node0] "CPUMask"=dword:00000003 “Group”=dword:00000000
[HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration
\Node1] "CPUMask"=dword:0000000C Group”=dword:00000000
[HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration
\Node2] "CPUMask"=dword:00000030 Group”=dword:00000001
[HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration
\Node3] "CPUMask"=dword:000000C0 Group”=dword:00000001
The CPUMask value is a bitmask of CPUs relative to the system. So Node0 described above includes CPUs 0 and 1, Node1
includes CPUs 2 and 3, and so on. There is no actual limit of 4 NUMA nodes; this is just an example.
Note
Although SQL allows configuration of Soft NUMA nodes that cross hardware NUMA node boundaries, this is not
recommended because it results in excessive remote memory accessing.