Optimizing SQL Server performance on multinode servers

Troubleshooting

Problem

Multinode system performance with Microsoft SQL Server may not be as expected.

Resolving The Problem

Source

RETAIN tip: H181544

Issue

Multinode system performance with Microsoft SQL Server may not be as expected.

Affected configurations

The system may be any of the following IBM Servers:

System x3950 E, type 8874, any model
System x3950 E, type 8879, any model
System x3950 M2, type 7141, any model
System x3950, type 8872, any model
System x3950, type 8878, any model
xSeries 440, type 8687, any model
xSeries 445, type 8870, any model
xSeries 455, type 8855, any model
xSeries 460, type 8872, any model
xSeries MXE-460, type 8874, any model

This tip is not option specific.

The system is configured with at least one of the following:

Microsoft Windows 2003 Server for 32-bit Servers, any service pack
Microsoft Windows 2003 Server for 64-bit Servers, any service pack
Microsoft Windows 2003 Server, Datacenter Edition, any service pack
Microsoft Windows 2003 Server, EE x64, any service pack
Microsoft Windows 2003 Server, x64 Edition, any service pack
Microsoft Windows Server 2008, Datacenter 32-bit, any service pack
Microsoft Windows Server 2008, Datacenter 64-bit, any service pack
Microsoft Windows Server 2008, Enterprise 32-bit, any service pack
Microsoft Windows Server 2008, Enterprise 64-bit, any service pack
Microsoft Windows Server 2008, Standard 32-bit, any service pack
Microsoft Windows Server 2008, Standard 64-bit, any service pack

Note: This does not imply that the network operating system will work under all combinations of hardware and software.

Please see the compatibility page for more information:

	http://www.ibm.com/servers/eserver/serverproven/compat/us/

Solution

Follow the recommendations listed in the "Optimizing SQL Server 2005/2008 Performance on Multi-Node Servers" .pdf file, which is available from the following URL:

	ftp://ftp.software.ibm.com/systems/support/system_x_pdf/H181544.pdf

Additional information

Topics covered in the attached white paper are listed below:

This paper will discuss tuning techniques for Microsoft SQL Server 2005 and 2008 to enable them to perform well on a multinode server, such as the IBM System x3950 and System x3950 M2. Tweaks that are not specific to multinode scalability, such as the use of large pages, are not discussed.

Introduction

A singlenode or non-scalable system based on recent Intel x86 processors has all its memory, slots, and I/O local to all the processors in the system. There is no performance penalty for using one area of memory over another.

The situation is different when we go to a multinode system. In that case, some of the memory, slots, I/O, and processors are local (on the same node) and some are remote (on a different node). Accessing remote resources incurs a performance penalty, which can be substantial.

When the operating system and application software are not configured properly to deal with this fact, system performance can suffer dramatically.

Other Topics

The Problem
The Solution
Setting up the SQL Affinity Mask
Setting up SoftNUMA Nodes
Setting up the SQL Server Network Connection Affinity
Non-NUMA Commands
Other Notes
Review

Document Location

Worldwide

Operating System

System x:Windows Server 2003

System x:Windows Server 2003 x86-64

Older System x:Windows Server 2003

Older System x:Windows Server 2003 x86-64

System x:Windows Server 2008

System x:Windows Server 2008 x86-64 & 2008 R2

Older System x:Windows Server 2008

Older System x:Windows Server 2008 x86-64 & 2008 R2

[{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"HW191","label":"Older System x->xSeries 440"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"HW19U","label":"Older System x->xSeries 445"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"HW21F","label":"Older System x->xSeries 455"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"HW21J","label":"Older System x->xSeries 460"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"HW315","label":"System x->System x3950"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"HW316","label":"System x->System x3950 E"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"HW333","label":"System x->System x3950 M2"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"QU02EHK","label":"System x->System x3950->8878"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"QU02EHY","label":"System x->System x3950 E->8879"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"QUOEDIB","label":"System x->System x3950 E->8874"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}},{"Type":"HW","Business Unit":{"code":"BU016","label":"Multiple Vendor Support"},"Product":{"code":"QUOER3U","label":"System x->System x3950->8872"},"Platform":[{"code":"PF033","label":"Windows"}],"Line of Business":{"code":"","label":""}}]

Tips

Optimizing SQL Server performance on multinode servers - Servers