0

I have SQL SERVER 2005 database running on windows server 2008 with following configuration SERVER:HP Proliant ML380 G7 CPU: 1 RAM: 10 GB of RAM HHD: 3*300 GB HHDs with RAID 5 configuration.

It has 10 live databases and 10 history databases with different sizes from 500MB to 6GB. These databases have around 100 users who will read and write.

There are a lot of reads than writes per second, I don't have the exact statistics.

Now We're trying to upgrade our server and thinking of better RAID configuration one for the data files and the other for OS ,log files and tempdb.

  • Can anybody suggest me which will be the best RAID for my system?

  • RAID1 or RAID5 or RAID10?.

  • Should I go for two RAIDs?

From my search I found RAID 10 is nice, but also somebody is saying 'advantage of RAID 10 is myth',refer below article.

http://www.zdnet.com/blog/ou/comprehensive-raid-performance-report/484

1

It would be very helpful to have such statistics. Usually, I try to estimate number of IOPS an application may require to run smoothly and to be responsive. With such information, I can then perform a kind of capacity planning to evaluate which RAID level is optimal to use and to estimate number of spindles/disks to achieve required IOPS (as each disk can provide a certain amount of IOPS based on its rotational speed, average latency and average seek time).

Just an illustrative example. According this document, MS SQL Server 2008 generates 29000 IOPS in 3557 SQL transactions (per second). Let's suppose we have a database with such IOPS required where 75% of requests are READ IOPS and 25% are WRITE IOPS (1 READ request or 1 WRITE request is 1 IOPS). This example won't count with cache hits.

Now, how many IOPS will have to provide underlying disk array/disks? We can use this simple formula to estimate it

TOTAL_IOPS = REQ_IOPS x READ_IOPS + REQ_IOPS x WRITE_IOPS x RAID_PENALTY

In our example, we have REQ_IOPS = 29000, READ_IOPS = 0.75 and WRITE_IOPS = 0.25. The only missing variable is so-called RAID penalty. A single SAS disk (of 15k RPMs) has around 200 IOPS in average. But when a disk is part of a RAID group we can’t get this amount of IOPS because some sort of calculation needs to be done to write data to the disk so that we can recover from a disk failure. So RAID1/10 (with 2 sub-mirrors) has penalty of 2 because there will be 2 writes to take place, one write to each of the disks while RAID5/50 has penalty of 4 because for each change to the disk, we are reading the data, reading the parity and then writing the data and writing the parity before the operation is complete. RAID6 has then penalty of 6.

What does it mean for us? Lets' have RAID10 configuration with RAID_PENALTY = 2

TOTAL_IOPS = 29000 x 0.75 +29000 x 0.25 x 2 = 36250 IOPS
TOTAL_DISKS = 36250 / 200 ~ 182 disks

In RAID5 configuration with RAID_PENALTY = 4

TOTAL_IOPS = 29000 x 0.75 +29000 x 0.25 x 4 = 50750 IOPS
TOTAL_DISKS = 50750 / 200 ~ 254 disks

The above example is showing us that achieving 29000 IOPS obviously requires more disks in RAID5 configuration than in RAID10. Another question is what is available disk capacity of such configurations? If our SAS disk has 500GB then in RAID10 configuration we will have 500 x 182 / 2 = 45.5TB while with RAID5 500 x (254 - 1) = 126.5TB (the same configuration with RAID10 would require 506 disks!!!). Pretty nice.

So do you need IOPS at the best price? Then go with RAID10 as it has the best price/IOPS ratio. Or do you prefer higher capacity but with similar IOPS? Then go with RAID5 as it has the best price/MB ratio.

Finally, this table summarizes common characteristics of various RAID levels and may help you to choose the right one:

enter image description here

As you can see RAID10 is the most suitable one from performance perspective. RAID5 doesn't perform well in writing as it has penalty of 4 but it provides better capacity.

1

RAID5 is rarely appropriate. At the very least, you should profile the performance with one drive pulled from the array. An array rebuild may require several hours, not including the time to get a replacement drive. While the array is down one drive, performance is typically lower.

I would go with RAID 10. It's not uncommon to have separate spindles for transaction logs, and they have sequential write characteristics. RAID 1 may be good for that.

0

AFAIK for SQL Server is recommended RAID 0, RAID 1 and RAID 5.
Check this link: 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.