Tuning SQL Server 2005 on Windows Integrity Servers

Page 4 of 13
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. Keep I/O rates to < 100/sec/spindle (10KRPM drives) or 120/sec/spindle (15KRPM drives)
2. 2 GB/s fiber channels can transfer ~180 MB /sec.
3. Ultra 320 SCSI channels can transfer ~240 MB/sec.
You must also consider the characteristics of the workload. OLTP workloads typically perform small,
random I/O operations, while Decision Support (DS) workloads (large queries) perform fewer but
larger I/O operations. With OLTP, you are more concerned with the I/O rate than the bandwidth;
the opposite is true for DS. Obviously, every application is different, and the I/O loads imposed on
the system by those applications are unique.
The Perfmon utility provides basic data about I/O rates and throughputs. By monitoring a
running application with this utility, you can get the information you need to design your I/O
configuration.
In addition to the I/O, you must configure the storage system. Configuration of the storage system is
beyond the scope of this document. But by keeping the preceding rules of thumb in mind, you can
configure the I/O to achieve the best performance that the system allows and gain valuable
information about storage requirements.
With FC HBA, use the StorPort driver instead of the Miniport driver
For best performance with the fibre channel HBA, use the (Emulex or QLogic) StorPort Fibre Channel
drivers. While using the StorPort.sys driver, you must use switch zoning or some other method of
segmentation.
Multi-path software improves the availability of data and eliminates single point of failure in SAN
components. Make sure you choose the Storport version compatible with your multi-path software:
If connecting to EVA, use HP StorageWorks Secure Path.
Note: Secure Path does not support the StorPort.sys driver supplied by Microsoft.
If connecting to XP, use HP StorageWorks Auto Path.
If connecting to EMC, use EMC PowerPath.
Verify that maximum queue depth is greater than or equal to the number
of spindles
When using the Emulex HBA, use the HBanyware utility. The queue depth is set per target or per
LUN. The default maximum queue depth (QueueDepth) is 20. Use the HBanyware utility to change the