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
6
Benefits of using HP ESO include:
• A drastic reduction in the time and complexity needed to configure and tune SQL Server on the DL980
• An easy to use interface that makes recommendations you can either accept in full, or pick and choose those which best
suit your needs
• A one-stop configuration utility that eliminates the need for multiple tools
• A means of ensuring that available system resources are allocated not just appropriately, but optimally
• Fewer support calls due to misconfigured systems
After installing HP ESO, users should run a data collection process that scans the entire solution (server, OS, SQL Server,
storage, and network infrastructure). HP ESO then evaluates the results of its audit and makes configuration
recommendations spanning the entire solution, including the following areas:
• SQL Server instance parameters  MAXDOP, CPU Affinity, Lightweight Pooling, and Min/Max Memory, Max Worker thread
Checkpoints, Lightweight Pooling, Lock Pages in Memory, Priority Boost, File Auto Growth, Trace Flags, Xp_CmdShell;
– For Analysis services: RandomFileAccessMode, ResourceMonitoringEnabled, enableROLAPDistinctCountonDataSource,
CordinatorQueryBalancing Factor, CordinatorQueryBoostPriorityLevel, Memory Limit (Low/Total), Process Group
Affinity, Flight Recorder, Process Min Threads, Vertipaq Memory Limit, Server Modes
• Database settings  AutoGrowth, Compatibility Level Number (Tabular mode in XVelocity), Column Store Index
• Storage settings  Database File/Log/TempDB locations, RAID Levels, Number of Drives, and Drive Sizes
• Network settings  Network Ports, NUMA/Interrupt CPU Affinities, Receive Side Scaling (RSS), NIC Teaming
• IO Card Configuration and Placement  for optimal PCI card placement based on I/O slot capability
• Software version information
HP ESO uses built-in WBEM-based instrumentation for future integration with other management tools such as HP System
Insight Manager. It also provides reporting pages that provide you with graphical displays of various data collection
parameters, for use in analysis and interpretation, and the ability to export these results to a file. HP ESO can save your
current configuration at any time, and with its ability to roll back to previously saved configurations, you can retrieve and
apply those configurations later, if necessary.
For a complete description of HP ESO features, along with instructions for installing, configuring, and using this powerful
tool, see the HP ESO User Guide at hp.com/go/proliant-DL980-docs.
HP ESO is available as a self-installing, self-extracting system update on the HP Smart Update CD. To download the latest
ISO image of this CD, go to hp.com/support/DL980G7, select the server and then the Windows Operating System version.
Click Software - CD-ROM, and then click the Download button next to “Smart Update QFE CD for x64.”
To download HP ESO separately, go to hp.com/support/DL980G7, select the server and then the Windows Operating
System version. Click Software – System Management, and then click the Download button next to “HP ProLiant DL980
Enterprise SQL Optimizer for Windows Server 2008 and 2012 x64 Editions.”
Operating system recommendations
The HP ProLiant DL980 scale-up x86 server supports Windows Server 2008, Windows Server 2008 R2, and Windows Server
2012. These operating systems are NUMA-aware and can fully leverage the resources of large x86 servers. While Windows
Server 2008 can manage up to 64 logical processors, Windows Server 2008 R2 can go even further (up to 256 logical
processors), and Windows Server 2012 increases this limit to 640 logical processors, thus providing unprecedented
workload size, scalability, and across-the-board availability and reliability.
Windows (all versions)
Recommendations for all versions of Windows include the following:
• Do not start Windows with the /debug boot option enabled. While useful for troubleshooting, the /debug option affects
performance.
• Disable all unnecessary services. Many of the services started by default are unnecessary. To disable these services,
right-click My Computer > Manage > Services and Applications > Services, and disable unused services such as Active
Directory. If you are unsure of a service, err on the side of caution and leave it enabled. You may need to experiment to
ensure optimum functionality.