Tuning SQL Server 2005 on Windows Integrity Servers

Page 11 of 13
Next create the port listening strings. You can use SQL Server Configuration Manager (under SQL
Server 2005, select Network Configuration > Protocols for MSSQLSERVER > TCP/IP > Properties >
IPAddresses > IPAll > TCP Port). You can also use regedit to modify the following key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]
The listening string format is:
Portno1[SoftNumaNodeMask],PortNo2[SoftNumaNodeMask2]… PortNoN[SoftNumaNodeMaskN]
So the 4 ports for the 4 Soft NUMA machines in this example would be:
1436[0x1],1437[0x2],1438[0x4],1439[0x8]
Note: Unlike SQL2000, that uses CPU masks directly in the listening string, SQL2005 specifies Soft
NUMA masks. In other words, SQL2005 employs a 2-level definition, while SQL2000 is only
one level.
The full regedit key is:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll]
"TcpPort"="1436[0x1],1437[0x2],1438[0x4],1439[0x8]"
"TcpDynamicPorts"=""
"DisplayName"="Any IP Address"
As with SQL Server 2000, 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
OSQL –E -Sservername,1436
Note that the default port, 1433, can be used as well. This results in establishing connection affinitiy
with all CPUs in the system. However the heavy load imposed by these connections will not perform to
the maximum potential of the system.
For Business Intelligence Workloads
Set “max degree of parallelism” to ideal #: Use the number of CPU’s per NUMA node or a multiple
of it as a first order approximation for complex queries. For simple queries, use the maxdop hint.
Generally, finding the ideal maxdop setting takes some experimentation. Also, the larger the system
and more processors available, the larger maxdop can be without affecting other applications.
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.
Partition tables, especially large fact tables.
Set ‘max worker threads’ = total number of DB connections + number of CPUs + 8: Depending on
your system configuration, setting max worker threads to a smaller value than default (255) can
sometimes improve performance. If using the sp_configure procedure to change the setting, you must