Choosing the optimal amount of disks for a RAID server configuration and block sizes can be a challenge.

Typical questions from the SAN\Storage guy to the DBA are:

“How many disks in a RAID configuration?”

“What is the optimal block size?”

“What MB\sec throughput?” etc

Here’s a formula to give the DBA some guidance. Can I stress that deciding on the amount of disks in a RAID configuration requires testing, with different types of activity. If you add the impact of SVC cache mechanism and HBA queue depth settings , then there is some extra work to be done.

Ultimately the DBA must create an IO profile for the database server. Perfmon has some great counters. Use these counters to calculate the IOPs required broken down into read\write.

I want the unerlying system to cope with a certain amount of IOPs at an acceptable speed. I work in conjunction with the storage guy to architect a storage solution to satisfy the IOps requirement.

You’ll need to decide what is a suitable length of time to monitor your database server. It could be 30 minutes or 24 hrs. It depends. I I prefer to monitor for longer periods on Production systems with a variety of activity. The reason is I’m looking for the peaks. I use the peak IOPs (read and write) to create a report for the storage guy.

Once you have this information use the formula , written in t-sql , to estimate the disks.

The formula will take into account the RAID penalty value for RAID 5 and RAID 10.

I’ve included some example iop figures.

declare @read_iops DECIMAL(10,2);
declare @write_iops DECIMAL(10,2);
declare @amount_of_disks DECIMAL (10,2);
declare @penalty DECIMAL(10,2); -- RAID5 = 4 ; RAID 10 = 2
declare @drivefactor DECIMAL(10,2); -- e.g 15k rpm FC drive = 180

SET @read_iops = 11;
SET @write_iops = 25;
SET @penalty = 4.00;
SET @drivefactor = 180;

SET @amount_of_disks  = (@read_iops + ((@write_iops) * @penalty))/@drivefactor;

PRINT @amount_of_disks;
   

The disk amounts for RAID server configuration are a guide for the storage expert. You may find with some very high activity reporting database servers - where IOPs are very high, the disk recommendation is very high. The challenge for the storage expert is to find the right number to fit satisfy acceptable IO and the budget.

I’ll be posting on how to define the IOPs tomorrow.

Republished from http://www.sqlserver-dba.com.