When scaling databases more than a few gigabytes (GB), it is important to have at least a basic understanding of RAID (Redundant Array of Inexpensive Disks) technology and how it relates to database performance.
These are the benefits of RAID:
Hardware RAID controllers divide read/writes of all data from Windows and applications such as Microsoft SQL Server into slices (usually 16 KB to 128 KB) that are spread across all disks participating in the RAID array. Splitting data across physical drives distributes the read/write I/O workload evenly across all physical hard disk drives participating in the RAID array. This increases disk I/O performance because the hard disks participating in the RAID array are all kept equally busy, instead of some disks becoming a bottleneck due to uneven distribution of I/O requests.
RAID provides protection from hard disk failure and accompanying data loss with two methods: mirroring and parity.
Mirroring is implemented by writing information onto two sets of drives, one on each side of the mirrored pairs of drives. If there is a drive loss with mirroring in place, the data for the lost drive can be rebuilt by replacing the failed drive and rebuilding the data from the failed drive's matching drive. Most RAID controllers can provide a failed drive replacement and rebuild from the other side of the mirrored pair while Windows and SQL Server are online (referred to as "Hot Plug" capable drives). Mirroring is the best performing RAID option when fault tolerance is required. Each SQL Server write in the mirroring situation costs two disk I/O operations, one to each side of the mirrorset. Mirroring also provides more fault tolerance than parity RAID implementations. Mirroring can sustain at least one failed drive and may be able to survive failure of up to half of the drives in the mirrorset without forcing the system administrator to shut down the server and recover from file backup. The disadvantage of mirroring is cost. The disk cost of mirroring is one drive for each drive of data. RAID 1 and its hybrid, RAID 0+1 are implemented through mirroring.
Parity is implemented by calculating recovery information about data written to disk and writing this parity information on the other drives that form the RAID array. If a drive fails, a new drive is inserted into the RAID array and the data on that failed drive is recovered by taking the recovery information (parity) written on the other drives and using this information to regenerate the data from the failed drive. RAID 5 and its hybrids are implemented through parity. The advantage of parity is low cost. To protect any number of drives with RAID 5, only one additional drive is required. Parity information is evenly distributed among all drives participating in the RAID 5 array. The disadvantages of parity are performance and fault tolerance. Due to the additional costs associated with calculating and writing parity, RAID 5 requires four disk I/O operations for each Windows NT and SQL Server write as compared to two disk I/O operations for mirroring. Read I/O operation costs are the same for mirroring and parity. Also, RAID 5 can sustain only one failed drive before the array must be taken offline and recovery from backup media must be performed to restore data.
A general rule is to stripe across as many disks as necessary to achieve solid disk I/O performance. Performance Monitor indicates whether there is a disk I/O bottleneck on a particular RAID array. Be ready to add disks and redistribute data across RAID arrays and/or SCSI channels as necessary to balance disk I/O and maximize performance.
Many hardware RAID controllers have some form of read and/or write caching. Take advantage of this available caching with SQL Server because it can enhance the effective I/O handling capacity of the disk subsystem. The principle of these controller-based caching mechanisms is to gather smaller and potentially nonsequential I/O requests coming in from the host server (hence SQL Server) and try to batch them with other I/O requests so the batched I/Os can form larger (32 KB to 128 KB) and maybe sequential I/O requests to send to the hard disk drives. This helps produce more disk I/O throughput given the fixed number of I/Os that hard disks can provide to the RAID controller. The RAID controller cache arranges incoming I/O requests by making the best use of the hard disks' underlying I/O processing ability.
RAID controllers usually protect their caching mechanisms with a form of backup power. The backup power can preserve the data written in cache for a period of time (perhaps days) in case of a power outage. And in production environments, the backup power can provide the database server greater protection by providing adequate UPS protection to the server to flush data to disk if power to the server is disrupted.
RAID 1 and RAID 0+1 offer the best data protections and best performance among RAID levels but costs more required disks. When cost of hard disks is not a limiting factor, RAID 1 or RAID 0+1 are the best RAID choices for performance and fault tolerance.
RAID 5 provides fault tolerance at the best cost but has half the write performance of RAID 1 and 0+1 because of the additional I/O that RAID 5 must do reading and writing parity information onto disk. RAID 5 is not as fault tolerant as RAID 1 and 0+1.
The best disk I/O performance is achieved with RAID 0 (disk striping with no fault tolerance protection), but because there is no fault tolerance with RAID 0, this RAID level typically can be used only for development database servers or other testing environments.
Many RAID array controllers provide the option of RAID 0+1 (also referred to as RAID 1/0 and RAID 10) over physical hard disk drives. RAID 0+1 is a hybrid RAID solution. On the lower level, it mirrors all data, like RAID 1. On the upper level, the controller stripes data across all of the drives, like RAID 0. Thus, RAID 0+1 provides maximum protection (mirroring) with high performance (striping). These mirroring and striping operations are transparent to Windows NT and SQL Server because they are managed by the RAID controller. The difference between RAID 1 and RAID 0+1 is on the hardware controller level. RAID 1 and RAID 0+1 require the same number of drives for a given amount of storage. For more specifics about RAID 0+1 implementation of specific RAID controllers, contact the hardware vendor that produced the controller.
This illustration compares RAID 0, RAID 1, RAID 5, and RAID 0+1. To hold four disks of data, RAID 1 (and RAID 0+1) needs eight disks, whereas Raid 5 needs five disks. Be sure to involve the appropriate hardware vendors to learn more about RAID implementation specific to the hardware running the database server.
Online RAID expansion is a feature that allows disks to be added dynamically to a physical RAID array while SQL Server is online, as long as there are hot-plug slots available. Many hardware vendors offer hardware RAID controllers capable of providing this functionality. Data is automatically restriped across all drives evenly, including the newly added drive, and there is no need to shut down SQL Server or Windows. You can take advantage of this functionality by leaving hot-plug hard disk drive slots free in the disk array cages. Thus, if SQL Server is regularly over-taxing a RAID array with I/O requests (this is indicated by disk queue length for the Windows logical drive letter associated with that RAID array), you can install one or more new hard disk drives into the hot-plug slot while SQL Server is still running. The RAID controller redistributes some existing SQL data to these new drives so that SQL data is evenly distributed across all drives in the RAID array. Then, the I/O processing capacity of the new drives (75 nonsequential/150 sequential I/Os per second, per drive) is added to the overall I/O processing capacity of the RAID array.
In Windows NT Performance Monitor, logical and physical disk objects provide the same information. The difference is that logical disks in Performance Monitor are associated with what Windows NT interprets as a logical drive letter. Physical disks in Performance Monitor are associated with what Windows NT interprets as a single physical hard disk.
To enable Performance Monitor counters, use the command diskperf.exe from the command line of the command-prompt window. Use diskperf -y so that Performance Monitor reports logical and physical disk counters when using hard disk drives or sets of hard disk drives and RAID controllers, without the use of Windows NT software RAID.
When running Windows NT software RAID, use diskperf -ye so that Performance Monitor correctly reports physical counters across the Windows NT stripe sets. When diskperf -ye is used in conjunction with Windows NT stripe sets, logical counters do not report correct information and must be disregarded. If logical disk counter information is required in conjunction with Windows NT stripe sets, use diskperf -y instead. With diskperf -y and Windows NT stripe sets, logical disk counters are reported correctly but physical disk counters do not report correct information and should be disregarded.
The effects of the diskperf -y command do not occur until Windows NT has been restarted.
Hardware RAID controllers present multiple physical hard disk drives that compose a single RAID mirror set or stripe set to the Windows operating system, as one single physical. The Windows NT Disk Administrator application is used to associate logical drive letters to the single physical disk and does not need to know how many hard disks are actually associated with the single hard physical that the RAID controller has presented to it.
You should know how many physical hard disk drives are associated with a RAID array so you can determine the number of disk I/O requests that the Windows operating system and SQL Server send to each physical hard disk drive. Divide the number of disk I/O requests that Performance Monitor reports as associated with a hard disk drive by the number of actual physical hard disk drives known to be in the RAID array.
To estimate I/O activity per hard disk drive in a RAID array, multiply the number of disk write I/Os reported by Performance Monitor by either 2 (RAID 1 and 0+1) or 4 (RAID 5). This accurately accounts for the number of I/O requests being sent to the physical hard disk drives. It is at this physical level that the I/O capacity for hard disk drives apply (75 nonsequential and 150 sequential per drive). But do not expect to calculate exactly how much I/O is hitting the hard disk drives when the hardware RAID controller is using caching, because caching can change the amount of I/O that is hitting the hard disk drives.
It is best to monitor on disk queuing unless I/O is causing a problem. The Windows operating system cannot see the number of physical drives in a RAID array, so to assess disk queuing per physical disk accurately, you must divide the disk queue length by the number of physical drives participating in the hardware RAID disk array that contains the logical drive being observed. Keep this number under two for hard disk drives containing SQL Server files.
For more information about SQL Server and RAID, see SQL Server Books Online.
Windows NT provides fault tolerance to hard disk failure by providing mirror sets and stripe sets (with or without fault tolerance) through the Windows NT operating system, instead of through a hardware RAID controller. The Windows NT Disk Administrator application is used to define either mirror sets (RAID 1) or stripe sets with parity (RAID 5). Windows NT Disk Administrator also allows the definition of stripe sets with no fault tolerance (RAID 0).
Software RAID uses more CPU resources because Windows NT is the component managing the RAID operations, versus the hardware RAID controller. Thus, performance with the same number of disk drives and Windows NT software RAID may be a few percent less than the hardware RAID solution if the system processors are near 100 percent utilized. But Windows NT software RAID generally helps a set of drives to service SQL Server I/O better than those drives would have been able to do separately. This can reduce the potential for an I/O bottleneck, leading to higher CPU utilization by SQL Server and better throughput. Windows NT software RAID can provide a better-cost solution for providing fault tolerance to a set of hard disk drives.
For more information about configuring Windows NT software RAID, see Online Help for Windows NT and SQL Server Books Online.