Database Reference
In-Depth Information
RAID 0
Since this RAID configuration has no fault tolerance, you can use it only in situations where the reliability of data is
not a concern. The failure of any disk in the array will cause complete data loss in the disk subsystem. Therefore, you
shouldn't use it for any data file or transaction log file that constitutes a database, except, possibly, for the system
temporary database called
tempdb
. The number of I/Os per disk in RAID 0 is represented by the following equation:
I/Os per disk = (Reads + Writes) / Number of disks in the array
In this equation,
Reads
is the number of read requests to the disk subsystem, and
Writes
is the number of write
requests to the disk subsystem.
RAID 1
RAID 1 provides high fault tolerance for critical data by mirroring the data disk onto a separate disk. It can be used
where the complete data can be accommodated in one disk only. Database transaction log files for user databases,
operating system files, and SQL Server system databases (
master
and
msdb
) are usually small enough to use RAID 1.
The number of I/Os per disk in RAID 1 is represented by the following equation:
I/Os per disk =(Reads + 2 X Writes) / 2
RAID 5
RAID 5 is an acceptable option in many cases. It provides reasonable fault tolerance by effectively using only one
extra disk to save the computed parity of the data in other disks, as shown in Figure
3-1
. When there is a disk failure
in RAID 5 configuration, I/O performance becomes terrible, although the system does remain usable while operating
with the failed drive.
Any data where writes make up more than 10 percent of the total disk requests is not a good candidate for RAID 5.
Thus, use RAID 5 on read-only volumes or volumes with a low percentage of disk writes.
The number of I/Os per disk in RAID 5 is represented by the following equation:
I/Os per disk = (Reads + 4 X Writes) / Number of disks in the array
As shown in this equation, the write operations on the RAID 5 disk subsystem are magnified four times. For each
incoming write request, the following are the four corresponding I/O requests on the disk subsystem:
•
One read I/O to read existing data from the data disk whose content is to be modified
•
One read I/O to read existing parity information from the corresponding parity disk
•
One write I/O to write the new data to the data disk whose content is to be modified
•
One write I/O to write the new parity information to the corresponding parity disk
Therefore, the four I/Os for each write request consist of two read I/Os and two write I/Os.
In an OLTP database, all the data modifications are immediately written to the transaction log file as part of
the database transaction, but the data in the data file itself is synchronized with the transaction log file content
asynchronously in batch operations. This operation is managed by the internal process of SQL Server called
the
checkpoint process.
The frequency of this operation can be controlled by using the recovery interval (min)
configuration parameter of SQL Server. Just remember that the timing of checkpoints can be controlled through the
use of indirect checkpoints introduced in SQL Server 2012.
