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

Introduction

The HP ProLiant DL980 Server stands at the pinnacle of HP’s line of scale-up x86 servers. The DL980 not only supports the

most recent Microsoft® Windows® 64-bit operating systems, but also incorporates all of the following technologies:

• The latest Intel® Xeon® extended 64-bit processors

• Quick Path Interconnect technology high speed links supporting a Non Uniform Memory Access architecture

• Industry standard I/O architecture based on PCI-e (and optionally, PCI-X) buses

• HP PREMA Architecture − for more information, see the technical overview at

h20195.www2.hp.com/V2/GetPDF.aspx/4AA3-0643ENW.pdf.

Because of this powerful synergy of proven technologies and engineering, the HP line of scale-up platforms is exceptionally

well-suited for workloads requiring high-performance processing, such as business intelligence and other line-of-business

applications.

Large databases at the core of these workloads quickly reach the 32-bit architectural limit of x86-based systems. But the

recent generation of HP servers implementing Windows 64-bit operating systems and architecture offer much greater

headroom and can therefore take advantage of scale-up x86 platforms. For example, the HP ProLiant DL980 running

Microsoft Windows Server 2008 R2 supports a maximum of 2 terabytes of main memory, 160 logical processors (with Intel

Xeon E7 family processors), and 16 PCI cards; Windows Server 2012 allows the HP ProLiant DL980 to run with up to 4 TB of

RAM using the 32 GB dimms.

The HP ProLiant DL980 is the ideal platform to capitalize on the advantages of Microsoft SQL Server 2008 (x64), SQL Server

2008 R2, and, most recently, SQL Server 2012. Both SQL Server 2008 R2 and SQL Server 2012 deliver increased security,

scalability, and availability to enterprise data and analytical applications, while making them easier to build, deploy, and

manage. Optimized for 64-bit addressing, they take advantage of advanced memory addressing capabilities for essential

resources such as buffer pools, caches, and sort heaps, thereby reducing the need to perform multiple I/O operations to

move data in and out of memory from disk. This greater processing capacity, without the penalties of I/O latency, means

greater application scalability.

Although SQL Server runs “out-of-the-box” on HP scale-up x64 servers, we recommend some tuning guidelines to

maximize performance and take full advantage of the capabilities of this platform. This document describes configuration

settings that represent current “best practices” in tuning an HP scale-up x64 system for Microsoft Windows Server (2008,

2008 R2, or 2012) and SQL Server (2008, 2008 R2, or 2012).

Note

Windows Server 2008/R2/2012 is used throughout this document to describe features common to Windows Server 2008,

Windows Server 2008 R2, and Windows Server 2012. SQL Server is used to describe features common to SQL Server 2008,

SQL Server 2008 R2, and SQL Server 2012. When the discussion refers to a specific version of either, that version is explicitly

stated.

Prerequisite reading and system configuration

This document focuses on SQL Server tuning for the HP ProLiant DL980. However, before doing any SQL Server tuning work,

users must install and configure their HP ProLiant DL980 server with either Windows Server 2008 SP2, Windows Server

2008 R2, or Windows Server 2012 using the recommendations given in the HP white paper Best Practices When Deploying

Microsoft Windows Server on the HP ProLiant DL980, available at

h20000.www2.hp.com/bc/docs/support/SupportManual/c02577023/c02577023.pdf.

It is particularly important that you install the following software on the system before attempting any SQL Server tuning:

• HP Service Pack for ProLiant (HP SPP) – Provides the HP firmware, drivers, and software for the platform

• HP Smart Update – Provides a single source for critical OS, security, and clustering Microsoft hotfixes (known as QFEs

Quick Fix Engineering), and HP DL980 System Providers as well as Enterprise SQL Optimizer (ESO). To download the

latest ISO image of the Smart Update 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.”