Database Reference
In-Depth Information
Because of the continuous write operation in the transaction log file for a highly transactional OLTP database,
placing transaction log files on a RAID 5 array will degrade the array's performance. Although, where possible, you
should not place the transactional log files on a RAID 5 array, the data files may be placed on RAID 5 since the write
operations to the data files are intermittent and batched together to improve the efficiency of the write operation.
RAID 6
RAID 6 is an added layer on top of RAID 5. An extra parity block is added to the storage of RAID 5. This doesn't
negatively affect reads in any way. This means that, for reads, performance is the same as RAID 5. There is an added
overhead for the additional write, but it's not that large. This extra parity block was added because RAID arrays are
becoming so large these days that data loss is inevitable. The extra parity block acts as a check against this in order to
better ensure that your data is safe.
RAID 1+0 (RAID 10)
RAID 1+0 (also referred to RAID 10) configuration offers a high degree of fault tolerance by mirroring every data disk
in the array. It is a much more expensive solution than RAID 5, since double the number of data disks are required
to provide fault tolerance. This RAID configuration should be used where a large volume is required to save data and
more than 10 percent of disk requests are writes. Since RAID 1+0 supports
split seeks
(the ability to distribute the read
operations onto the data disk and the mirror disk and then converge the two data streams), read performance is also
very good. Thus, use RAID 1+0 wherever performance is critical.
The number of I/Os per disk in RAID 1+0 is represented by the following equation:
I/Os per disk = (Reads + 2 X Writes) / Number of disks in the array
Using a SAN System
SANs remain largely the domain of large-scale enterprise systems, although the cost has dropped. A SAN can be
used to increase the performance of a storage subsystem by simply providing more spindles and disk drives to
read from and write to. Because of their size, complexity, and cost, SANs are not necessarily a good solution in
all cases. Also, depending on the amount of data, direct attached storage (DAS) can be configured to run faster.
The principal strength of SAN systems is not reflected in performance but rather in the areas of scalability, availability,
and maintenance.
Another area where SANs are growing are SAN devices that use Internet Small Computing System Interface
(iSCSI) to connect a device to the network. Because of how the iSCSI interface works, you can make a network device
appear to be locally attached storage. In fact, it can work nearly as fast as locally attached storage, but you get to
consolidate your storage systems.
Conversely, you may achieve performance gains by going to local disks and getting rid of the SAN. SAN systems
are extremely redundant by design. But, that redundancy adds a lot of overhead to disk operations, especially the type
typically performed by SQL Server: lots of small writes done rapidly. While moving from a single local disk to a SAN
can be an improvement, depending on your systems and the disk subsystem you put together, you could achieve even
better performance outside the SAN.
Using Solid State Drives
Solid-state drives are taking the disk performance world by storm. These drives use memory instead of spinning disks
to store information. They're quiet, lower power, and supremely fast. However, they're also quite expensive when
compared to hard disk drives (HDD). At this writing, it costs approximately $.03/GB for a HDD and $.90/GB for an
SSD. But that cost is offset by an increase in speed from approximately 100 operations per second to 5,000 operations
