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
3
In addition, installation of the latest HP System Providers is highly recommended: For systems running Windows Server
2008/R2, System Providers 9.0.5 or later is recommended; for Windows Server 2012, System Providers 9.1 or later is
required. In these systems, the logical processors may not be assigned in APIC order, but instead re-assigned based on
system measurements of Non Uniform Memory Architecture (NUMA) distances during kernel group formation. Installation of
the HP System Providers ensures that the Windows logical processors are set optimally during kernel group configuration.
The HP System Providers are available as a self-installing, self-extracting system update on the HP Smart Update CD.
Finally, HP also encourages users to read all of the relevant Microsoft tuning white papers. References and links to these
papers are found at the end of this document in “For more information” (page 20).
Server recommendations
The HP ProLiant DL980 scale-up x86 server has a processor-based architecture where each CPU socket is presented to the
OS as a separate NUMA node. The cores on each socket appear to the OS as separate CPUs, and with Hyper-threading
enabled, each core appears as two Logical Processors (LPs). Each CPU also has onboard local memory controllers that
manage the memory attached to that processor. Since accessing local memory is always much faster than accessing
remote memory, you must take some steps to maximize local access and minimize remote access in order to achieve the
best performance from a NUMA server.
BIOS settings
The ROM-Based Setup Utility (RBSU) is used to set certain configuration parameters at the BIOS or hardware level. Most of
the default settings are fine and the system will run satisfactorily with them, but extensive testing has shown that changing
some of the default settings yields higher performance with certain workloads.
You can access the RBSU Setup utility by pressing F9 at the ProLiant splash screen during the boot process. Listed below are
the BIOS settings that are critical for peak performance. These settings should be verified at the first available opportunity
and changed as shown, if necessary:
• System Options > Processor Options > Hyper-threading > Enable or Disable (see “Hyper-threading considerations” on
page 3)
• Power Management Options > HP Power Profile > Custom
• Power Management Options > HP Power Regulator > OS Control
• Power Management Options > Advanced Power Management Options > Minimum Processor Idle Power State > C1E
(Reduces power when possible, for performance + power savings) or NO C-states (when performance is highest priority;
power saving functionality is ignored)
• Advanced Options > Advanced Performance and Tuning Options > HW Prefetch > Enabled
• Advanced Options > Advanced Performance and Tuning Options > Adjacent Sector Prefetch > Enabled
• Advanced Options > Advanced System ROM Options > Address Mode 44-bit > Enabled
(This setting is for Windows Server 2008 R2 and Windows Server 2012 only, and is mandatory if ≥ 1TB RAM. On Windows
Server 2008 SP2, this setting should remain Disabled, since that OS uses a 40-bit address mode.)
Note
However these settings are configured, Microsoft Windows Server operating systems always recognize the system’s logical
processors. These are the total number of cores (when Hyper-threading is OFF), or the total number of processor threads
(when Hyper-threading is turned ON).
Hyper-threading considerations
With Intel Xeon 65xx and 75xx processors, each NUMA node (processor socket) can contain up to 8 CPU cores, and with the
new Intel E7 processor family, up to 10 cores. To extend processing capabilities even further, you can enable Intel Hyper-
threading and each core will appear as two logical processors to the OS. Likewise, a single physical processor with 10 cores
appears as 20 functional processors to both the OS and the DBMS.
HP ProLiant DL980 Servers ship with Hyper-threading enabled by default. Depending on the workload, Hyper-threading can
increase system performance by up to 40% (20% is typical). But in some cases it can increase contention and thereby cause
a drop in performance. Or if the workload does not have enough parallelism, Hyper-threading can actually increase
response time, since each Hyper-threaded CPU is effectively slower than a physical core. In short, you should always test
your particular workload with and without Hyper-threading before committing to its use.