In an era of large multi-disk storage systems, every SQL Server administrator and database developer has to have an understanding of how to tune disk systems for maximum performance. A number of the decisions you make about storage configuration can have either a beneficial or a negative impact on your database's performance. The important point to understand is that different kinds of databases benefit from different kinds of RAID, cluster sizes, I/O transfer sizes, and other configuration settings. Different applications also make different demands on the SQL Server databases that they connect to. You want to set your storage up differently for each.
You can't really know all of the parameters required beforehand without testing for them. For example, if Exchange pages in 4K chunks it makes sense to make sure that your clusters are some multiple of this value. If you evaluate your messaging system and find that the average message size is 30K, and that 90% of your traffic is in the range from 24K to 37K you might want to set your cluster size to either 36K or 40K. The goal is to have the most efficient use of storage and to minimize the amount of disk head travel that occurs.
Selecting a RAID level is similarly intricate. You might think on the surface that RAID 5, since it gives you fast performance and has redundancy, would be a good level. Unfortunately, most large databases can't take the time to rebuild RAID 5 if it fails. So many use RAID 0 (striped,
Another factor in selecting RAID levels is the amount of reads and writes that a database does. Different levels of RAID have different performance characteristics based on what they are meant to accomplish. A business intelligence database isn't writing much new data to disk. Mainly what the BI system is doing is reading data and processing it. However, an online transaction processing system is spending most of its time writing to disk and very little reading from it. Different applications therefore are favored by different types of RAID setups.
Therefore, consider your application, test, and examine the different disk tuning applications and you can squeeze more performance out of your systems, buy fewer systems, and improve your company's and your own bottom line.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
This was first published in March 2005