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

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. For example, an 8-processor system with two NICs has DPCs

for the NICs assigned to processor 7 and processor 6.

Syntax for the alter server command can be found at Microsoft’s SQL Server Books Online:

• For SQL Server 2008 R2: msdn.microsoft.com/en-us/library/ee210585.aspx

• For SQL Server 2012: msdn.microsoft.com/en-us/library/ee210585 (v=sql.110).aspx

The following example sets the affinity of SQL on a fully-loaded DL980, leaving it OFF of CPUs 2, 4, and 28, thereby allowing

the NIC interrupts to reside there:

alter server configuration set process affinity cpu=0,1,3,5 to 27, 29 to 127

Configuration changes like this take place immediately, and can be done with the workload active. Any threads on the

excluded processors will continue to run on those processors to completion, but no new threads will be assigned to the

excluded LPs.

Use data compression appropriately

Data compression is one of the new features introduced in SQL Server 2008. There are three different modes for

compression:

• Backup Compression: Turned OFF by default. Can be turned ON for a given database. Reduces storage media needs but

uses a great deal of processing power when compressing, and somewhat less when decompressing.

• Row-level Compression: Best used to compress data within a row. Uses fewer columns to store variable length data, such

as strings that vary in length from row to row.

• Page-level Compression: Best used when the value of one or more columns is the same in multiple rows. The redundant

data is stored once in the page and then reconstructed when read.

In all of these schemes there is a tradeoff between processing and storage performance. Use care in balancing the needs for

normal processing with the compression and decompression of the data. Prototyping the specific application of

compression within SQL is recommended.

Application-dependent SQL parameter recommendations

Use the sp_configure stored procedure to optimize resources. To modify the advanced configuration options, HP

recommends that you first set the Show Advanced Options property, then run RECONFIGURE and restart the SQL Server

instance, by entering the following:

1. sp_configure Show Advanced options

2. GO

3. RECONFIGURE

For OLTP workloads

Set Max Degree of Parallelism (MaxDOP) to 1. This option limits the number of processors used in parallel plan execution. If

you use the sp_configure stored procedure to change the setting, you must first set Show Advanced Options to 1. The

setting change takes effect immediately without a SQL instance stop and restart.

Note

Higher MaxDOP values can be used, of course, but OLTP performance degrades as this value rises to higher levels. As

always, you must experiment with your workload to find the optimal setting.