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

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.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]

The listening string format is:

Portno1[SoftNumaNodeMask],PortNo2[SoftNumaNodeMask2]…

PortNo N [SoftNumaNodeMask N]

So the four ports for the Soft NUMA machines in this example would be:

1436[0x1],1437[0x2],1438[0x4],1439[0x8]

Note

Unlike SQL2000 which uses CPU masks directly in the listening string, SQL Server specifies Soft NUMA masks. In other

words, SQL Server employs a two-level definition, while SQL2000 is only one-level.

Therefore, the full regedit key is:

[HKLM\SOFTWARE\Microsoft\Microsoft SQL

Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]

"TcpPort"="1436[0x1],1437[0x2],1438[0x4],1439[0x8]" "TcpDynamicPorts"=""

"DisplayName"="Any IP Address"

The port number is the point of connection between the client and a specific Soft NUMA node. So if a client application

wanted to connect to Soft NUMA Node 0, it would specify:

SQLCMD –E -Sservername,1436

Note

The default port, 1433, can also be used. This results in establishing connection affinity with all CPUs in the system.

However, the heavy load imposed by these connections will degrade the maximum potential of the system. Another

alternative to SQL destination port-based client affinity selection is to use multiple NICs and, for each one, set its own IP

addresses for SQL Server to use.

For business intelligence workloads

You should set MaxDOP (Maximum Degree Of Parallelism) to the ideal number. Use the number of CPUs per NUMA node, or

a multiple of it, as a first order approximation for complex queries. For simple queries, use the MaxDOP hint. Finding the

ideal MaxDOP setting takes some experimentation. For example, you could start with the number of CPUs per NUMA node

(4, 6, 8 or 10), and then use the MaxDOP hint. Also, the larger the system and the more processors available, the larger

MaxDOP can be without affecting other applications.

It is advisable to use Tuning Advisor to optimize indices. Defining proper indices can have the most impact on large queries.

Creation, tuning, and rebuilding of indices must be done carefully for maximum affect.

When it comes to partition tables, especially large fact tables, it is preferable to break them down into manageable-sized

chunks so they are spread across different disks. This improves query response and seek times, data loading, purging, and

other system management operations. These improvements can also be accomplished using indexes.

A guideline for setting the Max Worker Threads value is to make it equal to the total number of DB connections + the

number of CPUs + 8. Depending on your system configuration, setting Max Worker Threads to a value smaller than the

default (255) can sometimes improve performance. 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.