Tuning SQL Server 2005 on Windows Integrity Servers

ManualsBrandsHP ManualsSoftwareMicrosoft Windows Server 2003 for Itanium-based Systems

Tuning SQL Server 2005 on HP Integrity Servers

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

Server ................................................................................................................................................ 2

Test your Workload before you select Hyper-Threading ........................................................................ 3

Set Cell Local Memory to 100%......................................................................................................... 3

Storage .............................................................................................................................................. 4

Provide sufficient I/O and storage to run the application ...................................................................... 4

With FC HBA, use the StorPort driver instead of the Miniport driver ....................................................... 4

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

Verify the port speed on the switch is set to the maximum ..................................................................... 5

Network ............................................................................................................................................. 5

Enable options to offload processing .................................................................................................. 5

Windows Server 2003......................................................................................................................... 6

SQL Server 2005 ................................................................................................................................ 6

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

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

Segregate Network processing from SQL processing............................................................................ 8

Set application-dependent SQL parameters............................................................................................. 8

For OLTP Workloads ........................................................................................................................ 8

For Business Intelligence Workloads................................................................................................. 11

Conclusion........................................................................................................................................ 12

Additional Resources.......................................................................................................................... 13

Summary of content (13 pages)

PAGE 1
Tuning SQL Server 2005 on HP Integrity Servers Introduction......................................................................................................................................... 2 Server ................................................................................................................................................ 2 Test your Workload before you select Hyper-Threading ........................................................................ 3 Set Cell Local Memory to 100%.
PAGE 2
Introduction The HP Integrity server family includes a wide range of scalable servers that run Microsoft Windows Server 2003. The Windows Integrity platform – based on the Microsoft Windows 64-bit operating system, Intel Itanium® 2 processor, HP system design, and industry standard I/O architecture based on PCI-X buses -- is exceptionally well-suited for workloads such as business intelligence and line-ofbusiness applications that demand high-performance computing.
PAGE 3
Test your Workload before you select Hyper-Threading With the release of the Dual Core Itanium 2 processor (Montecito), each cell can contain 8 cores. Furthermore, each core can be hyper-threaded, so the 8 cores might appear as 16 functional processors to the OS and to the DBMS. HP Integrity servers are shipped with hyper-threading OFF. Depending on the workload you run, turning hyper-threading ON can increase performance by 510% but could also increase contention.
PAGE 4
Storage Provide sufficient I/O and storage to run the application A common mistake in setting up a SQL server is to under-provision the I/O links to storage. For example, although an XP12000 array can function with a single fiber link to the system (an extreme case) this clearly does not provide adequate performance. Therefore, it is important to add enough fiber channel or SCSI links to handle the bandwidth needed by the I/O load. Use the following rules of thumb to size the I/O configuration: 1.
PAGE 5
value to a number greater than or equal to the number of spindles. Emulex driver options set in HBanyware utility should be greater than or equal to the number of physical drives (default = 20). When using the Qlogic HBA, use the SanSurfer utility. Use this utility to change the Qlogic firmware BIOS setting for execution throttle in NVRAM can be changed using the SanSurfer utility to be equal to or greater than the number of physical drives (default = 16).
PAGE 6
Windows Server 2003 Add the following new keys to the Windows Server 2003 OS installation using the Registry Editor.
PAGE 7
3. 4. 5. 6. 7. Note: Expand Security Settings, and then expand Local Policies. Select the Users Rights Assignment check box. The policies are displayed in the details pane. In the details pane, double-click Lock pages in memory. In the Local Security Policy Setting dialog box, click Add. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
PAGE 8
maximum write performance on the log. This checkbox can be selected while the system is running without stopping the server. The performance change is immediate and does not require a reboot. 6. Click OK and select the device again to verify that this box is still checked. Note: Any change in log disk hardware or cache configuration can cause the OS to unselect this checkbox.
PAGE 9
– Average Log Write Size of >30K can indicate a bottleneck. This may indicate the cache is disabled, or more log disks or larger cache size are required. Spread all tables over multiple files and over multiple disks (the more spindles the better). Do not software stripe LUNs that are already hardware striped. While this makes them easier to manage, it degrades performance considerably. Drive considerations: • If database drive latency > 20ms, more spindles are indicated.
PAGE 10
Use Connection Affinity to take further advantage of SQL Server NUMA features: Using connection affinity, a SQL connection from the client is assigned affinity to a specific NUMA node. This assigns data structures to that NUMA node, further 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.
PAGE 11
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.
PAGE 12
change show advanced options to 1. The setting takes effect immediately without a SQL instance stop and restart. If an application uses TempDB, put TempDB on fast disks (write cache enabled) that are large enough to support the data traffic (i.e., avoid auto-grow). Spreading TempDB to multiple files also helps improve performance. A general rule is one TempDB file per CPU. Conclusion HP Integrity servers provide powerful computing and memory resources.
PAGE 13
Additional Resources http://www.hp.com/go/integrity http://www.hp.com/support/itaniumservers/ © 2006 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty.