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
7
Note
Windows Server 2008 supports a maximum of 64 logical processors. For this reason, on DL980s with 8 processors and 8
cores or more, it is often better to disable Hyper-threading. In order to fully utilize all of the processors in these systems it is
preferable to run Windows Server 2008 R2 or Windows Server 2012 instead. You can then determine whether your
workload performs better with Hyper-threading enabled or disabled on Windows Server 2008 R2 and Windows Server
2012.
For more information about this issue, refer to the HP white paper Best Practices When Deploying Microsoft Windows Server
on the HP ProLiant DL980 at hp.com/go/proliant-DL980-docs.
Windows Server 2008 SP2 and Windows Server 2008 R2
Some of the Windows Server OS registry entries related to system behavior can affect performance. Refer to the Microsoft
performance tuning guidelines for more information, using the links provided at the end of this document. HP recommends
that you test these registry modifications in your environment to see how much benefit they deliver, and then decide
whether to implement them. These performance tuning guidelines are intended for advanced administrators only, so
consider them carefully.
Possible registry modifications include the following:
• Allocate PFN database sparsely and with small pages:
\HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\
Memory Management\LargePageMinimum = REG_DWORD 0xffffffff
• Verifier.sys - Disable random verification of drivers for debugging:
(normally disabled by default, but verify; performance is adversely affected if enabled)
\HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\
Memory Management\DontVerifyRandomDrivers = REG_DWORD 0x00000001
• Disable PartMgr/FltMgr/VolMgr Ioctl counters:
(implement only if no applications use these counters)
\HKLM\SYSTEM\CurrentControlSet\Services\PartMgr\
EnableCounterForIoctl = REG_DWORD 0x0
• Disable NTFS creation of 8.3 filenames:
(make sure this is compatible with applications)
\HKLM\SYSTEM\CurrentControlSet\Control\FileSystem\
NtfsDisable8dot3NameCreation = REG_DWORD 0x00000001
Windows Server 2008 R2 and Windows Server 2012
As stated previously, Windows Server 2008 R2 and Windows Server 2012 are the recommended OS for the DL980 because
it can support up to 160 logical processors when Hyper-threading is enabled on servers with 8 processors and 10 cores per
processor. To complement the previously recommended global BIOS settings, there is an additional High Performance
Power option that is available when using Windows Server 2008 R2 or Windows Server 2012 with SQL Server.
To select this High Performance Power option in Windows Server 2008 R2 or Windows Server 2012, follow these steps:
1. Click Start > Control Panel.
2. Select View > Large Icons.
3. Click Power Options.
4. Select High Performance.
In addition, the following power configuration commands allow you to disable C-states in Windows (similar to choosing the
NO C-states option at the BIOS level, as described earlier). These commands do not require a system reboot:
• powercfg -setacvalueindex scheme_min sub_processor 5d76a2ca-e8c0-402f-a133-
2158492d58ad 1
• powercfg -setacvalueindex scheme_max sub_processor 5d76a2ca-e8c0-402f-a133-
2158492d58ad 1