Although many database administrators don’t consider hardware infrastructure as part of database operations, storage configuration absolutely can impinge upon database performance. The storage options you’re using in association with SQL Server may be affecting its performance. This is particularly possible if you are using a redundant array of independent disks or RAID—a disk system that contains multiple disk drives.
Why Use RAID?
RAID systems are typically used in conjunction with read-write intensive applications to achieve better performance in light of the frequency of I/O operations that are an inherent function of these types of applications. The RAID array used in conjunction with SQL needs to be carefully considered.
There are many RAID arrays available, and each of the six levels uses a different algorithm for fault tolerance. Most often, RAID 0, 1, and 5 are used with SQL Server.
- RAID Level 0 is known as disk striping. It uses a disk file system known as a stripe set. The data is divided into blocks and spread among all the disks in an array. Operations are also spread across disks, improving performance, since many operations can be performed both independently and at the same time.
- RAID Level 1 is referred to as disk mirroring. It provides a redundant, identical copy of a selected disk. All data written to the main disk in the array is also written to the mirror disk. Microsoft notes that, although this level does provide fault tolerance and generally improves read performance, the write performance may be degraded.
- RAID Level 5 is most frequently used. This is known as striping with parity, as the data is “striped” in large blocks across the disks in the array. The parity is also written across all the discs, which also means the data is redundant. Microsoft explains that the data and parity information are arranged such that they are always on different disks. Although the performance is better, it is not perfect. If, notes Microsoft, “a stripe member is missing, read performance is decreased, for example, when a disk fails.”
Which RAID Level to Use?
But which level or approach is most suitable for your SQL environment? Suprotim Agarwal explains:
The answer to this question depends on a variety of factors. Do you want availability, performance or cost? What are your requirements for fault tolerance and performance? …When it comes to a database like SQL Server, no one RAID level will suit your need.
In weighing the decision, you should consider the number of write and read options. RAID 10, for example, would be a good option for those databases with more write operations. Other factors include the other files being stored, including database transaction log files and backup and recovery files. You will also need to consider the possibility more storage might be needed to implement a new storage or backup strategy and the potential costs associated with it.
More generally, Microsoft TechNet suggests:
Data striping (RAID 0) is the RAID configuration with the highest performance, but if one disk fails, all the data on the stripe set becomes inaccessible. A common installation technique for relational database management systems is to configure the database on a RAID 0 drive and then place the transaction log on a mirrored drive (RAID 1). You can get the best disk I/O performance for the database and maintain data recoverability (assuming you perform regular database backups) through a mirrored transaction log.
For any data that has to be rapidly recoverable, RAID 5 is the better option. This level of RAID gives users full redundancy of all the data on the array. This is useful if a single disk happens to fail. The disk can be easily replaced, most often, without any system downtime. Although it does not have the same performance as RAID 0 or RAID 1, using RAID 5 does provide users with higher reliability and faster recovery.
Microsoft explains each RAID level in detail on the TechNet page.
If you need help with performance tuning or reconfiguring your SQL Server storage, please feel free to contact Datavail to discuss a custom solution designed for your enterprise. You can learn more about our remote database services and how our experts can help with your ongoing SharePoint operations. For more solutions to common and advanced DBA related questions, head over to Datavail’s frequently updated blog.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?