A typical hard disk provides a maximum transfer rate of about 40 MB per second or 75 nonsequential/150 sequential disk transfers per second. Typical RAID controllers have an advertised transfer rate of about 40 MB per second or approximately 2,000 disk transfers per second. PCI buses have an advertised transfer rate of about 133 MB per second and higher. The actual transfer rates achievable for a device usually differ from the advertised rate. You should understand how to use these transfer rates as a starting point for determining the number of hard disk drives to associate with each RAID controller and in turn, how many drives and RAID controllers can be attached to a PCI bus without I/O bottlenecks.
The maximum amount of SQL Server data that can be read from or written to a hard disk drive per second is 9.6 MB. Assuming a RAID controller can handle 40 MB per second, you can calculate the number of hard disk drives that should be associated with one RAID controller by dividing 40 by 9.6 to get about 4. This means that at most 4 drives should be associated with that one controller when SQL Server is doing nothing but sequential I/O of 64 KB. Similarly, with all nonsequential I/O of 64 KB, the maximum data sent from the hard disk drive to the controller is 4.8 MB per second. Dividing 40 MB per second by 4.8 MB per second gives approximately 8 as the result. This means that at most 8 hard disk drives should be associated with the single controller in the nonsequential 64-KB scenario. The random 8-KB data transfer scenario requires the most drives. Divide 40 by 0.6 to determine that about 66 drives are needed to saturate a RAID controller doing 100 percent random 8-KB reads and writes. This is not a realistic scenario because read-ahead and log-writing use transfer sizes greater than 8-KB and it is unlikely that a SQL Server will perform 100 percent random I/O.
You can also determine how many drives should be associated with a RAID controller by looking at disk transfers per second instead of looking at the megabytes per second. If a hard disk drive is capable of 75 nonsequential (random) I/Os per second, then about 26 hard disk drives working together could theoretically produce 2,000 nonsequential I/Os per second, or enough to hit the maximum I/O handling capacity of a single RAID controller. Alternately, it takes only about 13 hard disk drives working together to produce 2,000 sequential I/Os per second and keep the RAID controller running at maximum throughput because a single hard disk drive can sustain 150 sequential I/Os per second.
RAID controller and PCI bus bottlenecks are not as common as I/O bottlenecks related to hard disk drives. For example, assume a set of hard disk drives associated with a RAID controller is busy enough to push 40 MB per second of throughput through the controller. The next consideration is how many RAID controllers can be attached safely to the PCI bus without risking a PCI bus I/O bottleneck. To make an estimate, divide the I/O processing capacity of the PCI bus by the I/O processing capacity of the RAID controller: 133 MB/sec divided by 40 MB/sec results in approximately three RAID controllers that can be attached to a single PCI bus. Most large servers come with more than one PCI bus, which increases the number of RAID controllers that can be installed in a single server.
These calculations illustrate the relationship of the transfer rates of the components that comprise a disk I/O subsystem (hard disk drives, RAID controllers, PCI bus) and are not literal figures. These calculations assume all sequential or all nonsequential data access, which is not likely in a production database server environment. In reality, a mixture of sequential, nonsequential, 8-KB and 64-KB I/O occurs. Additional factors influence how many I/O operations can be pushed through a set of hard disk drives at one time. On-board read/write caching available for RAID controllers increases the amount of I/O that a set of drives can effectively produce. How much more is difficult to estimate for the same reason that it is difficult to determine an exact number an 8-KB versus a 64-KB I/O SQL Server environment needs.