Disk tuning for SQL Server

Consider your SQL Server's disk storage configuration to achieve maximum performance.

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, no parity) and often mirror with RAID 0+1. If your system is mission critical, even RAID 0+1 might not be adequate and you might prefer to use RAID 0 augmented by a storage server that creates what EMC called Business Continuance Volumes or BCVs. When your primary disk system goes down, the system automatically switches over to an alternate identical system where any recent changes that haven't been propagated to the BCV are updated from a heavily redundant cache.

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

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close