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

ManualsBrandsHP ManualsServerHP ProLiant DL980 G7 X6550 2.0GHz 8 Core 4P Rack Server

Technical white paper

Best Practices for Tuning

Microsoft SQL Server on the

HP ProLiant DL980

Table of contents

Introduction .................................................................................................................................................................................... 2

Prerequisite reading and system configuration ..................................................................................................................... 2

Server recommendations ............................................................................................................................................................ 3

BIOS settings .............................................................................................................................................................................. 3

Hyper-threading considerations ............................................................................................................................................ 3

Card placement: Understanding processor enumeration and I/O slots ....................................................................... 4

Recommended software: HP Enterprise SQL Optimizer (HP ESO)................................................................................. 5

Operating system recommendations ....................................................................................................................................... 6

Windows (all versions) ............................................................................................................................................................. 6

Windows Server 2008 SP2 and Windows Server 2008 R2 .............................................................................................. 7

Windows Server 2008 R2 and Windows Server 2012 ...................................................................................................... 7

Storage recommendations ......................................................................................................................................................... 8

Provide sufficient I/O and storage to run SQL Server ....................................................................................................... 8

Use the recommended Storport driver with fibre channel host bus adapters............................................................ 9

Verify maximum queue depth is greater than or equal to the number of spindles................................................... 9

Verify switch port speed is set to maximum ..................................................................................................................... 10

Network recommendations ...................................................................................................................................................... 10

Configure receive side scaling (RSS) ................................................................................................................................... 10

Enable options for offload processing ............................................................................................................................... 11

SQL Server tuning recommendations ..................................................................................................................................... 12

Use startup options to lock pages in memory.................................................................................................................. 12

Use SQL Server startup flags ................................................................................................................................................ 13

Ensure SQL Server starts up immediately after system boot ....................................................................................... 13

Enable write caching on the log disk .................................................................................................................................. 13

Segregate network processing from SQL processing .................................................................................................... 14

Use data compression appropriately ................................................................................................................................. 15

Application-dependent SQL parameter recommendations............................................................................................... 15

For OLTP workloads ............................................................................................................................................................... 15

For business intelligence workloads .................................................................................................................................. 18

Conclusion ..................................................................................................................................................................................... 19

For more information ................................................................................................................................................................. 20

Documentation feedback .......................................................................................................................................................... 20

Summary of content (20 pages)

PAGE 1
Technical white paper Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Table of contents Introduction .................................................................................................................................................................................... 2 Prerequisite reading and system configuration ..................................................................................................................... 2 Server recommendations ..............
PAGE 2
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.
PAGE 3
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 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.
PAGE 4
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Card placement: Understanding processor enumeration and I/O slots The following diagram illustrates the physical layout of I/O slot locations and other components, relative to the DL980 processor boards. Figure 1. I/O slots vs. processor boards Embedded adapters (like network card NC375i, video, and so on) are connected to processors 0 (zero) and 1.
PAGE 5
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Table 1.
PAGE 6
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 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 allo
PAGE 7
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 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.
PAGE 8
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 • powercfg -setacvalueindex scheme_balanced sub_processor 5d76a2ca-e8c0-402f-a133- 2158492d58ad 1 • powercfg -setactive scheme_current Note To revert any power configuration command back to its previous state, rerun the command with a 0 (zero) at the end instead of a 1.
PAGE 9
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Fibre Channel transfer speeds Fibre Channel transfer speeds are as follows: • 2 Gbps fibre channel transfer rate = ~180 MB/sec • 4 Gbps transfer rate = 350 MB/sec • 8 Gbps transfer rate = 680 MB/sec Also be aware that for 8 KB I/O rates, many fibre channel host bus adapters (HBAs) have a limit on throughput below the fibre channel bandwidth.
PAGE 10
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Note While increasing the queue depth often provides benefits for a given server, it can also have detrimental effects on other servers utilizing the same storage array. You should refer to the HP SAN Design Guide and consider the potential impact to other servers if you increase the queue depth beyond the values recommended in the HP SAN Design Guide.
PAGE 11
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Other advanced properties can be enabled too, depending upon driver implementation. These properties correspond to the registry entries described in the following table: Table 2.
PAGE 12
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 interrupts). In general, the former is recommended unless the application requires extremely low latencies. If in doubt, a good compromise is to set the interrupt moderation to Adaptive.
PAGE 13
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Use SQL Server startup flags There are several SQL Server Trace flags you can use to increase SQL performance. These have proven useful in large systems like the DL980. • T834 − Use Large Pages When combined with Lock Pages in Memory option described above, this flag forces SQL to use 2 MB pages for the buffer cache instead of the default 4 KB pages.
PAGE 14
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Figure 2. Policies tab for the log disk(s) Note If the cache is enabled at the hardware level, the first checkbox is usually selected but the second is not. The second checkbox is critical for maximum write performance on the log. These checkboxes can be selected while the system is running. The performance change is immediate and does not require a reboot. 7.
PAGE 15
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Note Soft NUMA can also accomplish this task. See “Other considerations” (page 16). Before changing the affinity settings, keep in mind that the OS assigns deferred procedure call (DPC) activity associated with NICs to the highest numbered processor in the system. In systems with more than one active NIC, each additional card’s activity is assigned to the next highest numbered processor.
PAGE 16
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 You must also check the transaction log latency and write size. Since every transaction committed in SQL must be written and committed to the log, the SQL log can easily become a bottleneck and limit system performance. A quick check with the Windows Performance Monitor utility can easily verify this. Log Write Service times should be very low (about 1ms).
PAGE 17
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 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.
PAGE 18
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Second, create the port listening strings using SQL Server Configuration Manager. Under SQL Server, select Network Configuration > Protocols for MSSQLSERVER > TCP/IP > Properties > IPAddresses > IPAll > TCP Port. You can also use regedit to modify the following key in the system registry: [HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL10_50.
PAGE 19
Technical white paper | Best Practices for Tuning Microsoft SQL Server on the HP ProLiant DL980 Additionally, if an application uses the TempDB database, you should put TempDB on fast disks with write cache enabled. These disks must be large enough to support the traffic (for example, avoid the AUTOGROW option). Spreading TempDB to multiple files also helps improve performance. A general rule to follow is one TempDB file per CPU.
PAGE 20
For more information HP ProLiant DL980 servers: hp.com/servers/DL980 Performance Tuning Guidelines for Windows Server 2008: msdn.microsoft.com/en-us/windows/hardware/gg463394.aspx Performance Tuning Guidelines for Windows Server 2008 R2: msdn.microsoft.com/en-us/windows/hardware/gg463392.aspx Performance Tuning Guidelines for Windows Server 2012: msdn.microsoft.com/library/windows/hardware/jj248719 For additional best practices white papers for the HP ProLiant DL980: hp.