System and Storage Configuration for SQL Server
The figures below shows the sequential disk read and write characteristics for a current generation 36GB 15 SCSI drive. The front portion of a 15K drive can transfer data at over 90MB/sec. The inner portion performance is still over 50MB/sec. Data placement can affect performance. A common practice is to use the front portion of each disk drive for the most performance critical data, the middle for less critical data and the end for backup and auxiliary storage.
The choice of the RAID controllers then falls to either dual or quad channel controllers. The PCI-X bus at 133MHz and 64-bit has a nominal bandwidth of 1GB/sec, which might seem appropriate for four U320 channels capable of sustaining approximately 250MB/sec each. However, it is not clear that any of the current quad-channel PCI-X SCSI controllers can actually sustain more the 500-600MB/sec. So the best choice for controllers is the dual channel model unless very high spindle count is required that would exceed the capacity of dual channel controllers.
Fiber Channel / SAN Storage
Fiber channel was designed for the high-end storage market. For some reason, the FC/SAN vendors did not continue to increase FC bandwidth from 2Gbit/sec to 4Gbit/sec in an expeditious time frame. In the mean time, U320 SCSI is being replaced by 3Gbit/sec SAS and even the original SATA 1.5Gbit/sec is being replaced with an improved 3Gbit/sec version. One might argue that FC at 4Gbit/sec, finally becoming available after a long pause since the 2Gbit/sec launch, is now faster than SAS and SATA. However the SAS and SATA 3Gbit/sec links are meant to connect 1-4 disk drives, while SAN system intend to cram 14-28 disks over a single 2-4Gbit/sec link, severely constraining sequential bandwidth.
Another issue with mid-range SAN systems is that most adapted components from existing server systems, either the Xeon processor with the ServerWorks GC-LE chipset, or RISC processors and chipsets with even less capability. At the time, none of the existing chipsets could deliver massive IO bandwidth like chipsets today can. So the mid-range SAN systems from 2003-2005 were not the most practical solutions for delivering high sequential bandwidth at a reasonable price point compared with direct attach storage options.
- If available, 4Gbit/sec Fiber Channel is preferred over 2Gbit.sec FC.
- Dual channel controllers should be employed with 2Gbit/sec FC on PCI-X and 4Gbit/sec FC on PCI-X 2.0 or PCI-Express.
- 1-2 FC ports per 14 disk external storage unit.
For example: in the EMC Clarion line, the CX 700 supports 8 FC ports to hosts and 8 ports to storage. Configuring 8 racks of 14 drives over 8 FC ports from storage to SAN to host should deliver nearly the full 1.3GB/sec maximum sustained transfer rate of the CX 700. For the HP EVA line, the underlying storage controllers are the HSV110/210, which have 2 ports to hosts, and 4 ports to storage. Each rack of 14 disks connected to a single HSV controller can deliver 300MB/sec sequential bandwidth, but more drives are required for high write bandwidth. For best overall performance and practicality, 2 racks of disks per HSV controller is probably the better balance over 1 disk rack per HSV controller.
Serial Attached SCSI (SAS)
The recommended configuration for the newer SAS technology drives is not yet clear. Of the major vendors, only HP currently offers SAS with the external storage unit holding 10 SFF drives in a 1U enclosure. The SAS RAID controller has 8 3Gbit/sec ports. Based on current disk drives, 2-3 disk drives per port is probably reasonable, pending verification that a single adapter can handle the combined sequential throughput of 12-18 disk drives. Otherwise consider 1-2 disk drives per port. Two 6 port adapters, 2 1U storage units and the internal bays should meet the basic SQL Server 2000 storage performance level. When configuring for higher sequential transfer rate, verify the bandwidth possible in each configuration to determine the best controller – disks combination.
SATA Controllers and Disk Drives
Many people today ask about SATA controllers and disks drives, especially now that native command queuing (NCQ) is now finally supported by both SATA controllers and disk drives. The low cost and high capacity of SATA drives is particularly attractive. The main issue is that most SATA drives are 7200RPM drives targeted at the desktop market. The design life of a desktop drive is typically 1-2 years at 20% duty cycle, while most server drives are designed for 5 years at 100% duty cycle. Primarily for this reason, SATA drives should be restricted to development environments, possibly some data warehouse environments, and potentially as backup storage until more about SATA drive reliability is better understood. SATA drives should not be used in production transaction processing environments at this time.
The main points are as follows. The 64-bit version of Windows Server 2003 has much expanded memory capability, even in Standard Edition. SQL Server 2005 Standard has removed memory constraints. The 64-bit version has full access to large address space, while the 32-bit version must use the PAE/AWE combination. Dual core processors are preferred over single core processors in most circumstances due to the favorable licensing terms. Storage configuration should factor random and sequential IO performance as the primary consideration. This means distributing IO load over as many spindles (disk drives) and IO channels as practical for the system. As always, verify the actual performance characteristics of the final configuration.