SQL SERVER – Raid Configuration – RAID 10

I get question about what configuration of redundant array of inexpensive disks (RAID) I use for my SQL Servers.

The answer is short is: RAID 10. Why? Excellent performance with Read and Write.

RAID 10 has advantage of both RAID 0 and RAID 1. RAID 10 uses all the drives in the array to gain higher I/O rates so more drives in the array higher performance. RAID 5 has penalty for write performance because of the parity in check. There are many article already written about them. If you are interested in reading more please refer book online.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL

23 thoughts on “SQL SERVER – Raid Configuration – RAID 10

  1. Surely the RAID array described in the ‘Book online’ link above is RAID 0+1 not RAID 10 (1+0)
    RAID 0 (striped) Mirrored to another RAID 0 i.e stripe two sets of disks then mirror the stripes

    RAID 10 as I understand it is a RAID 0 Array of RAID 1 Mirrored pairs. i.e. Mirror pairs of disks, then stripe the mirrored pairs.

    In RAID 0+1, any one drive failing from each RAID-0 array (2 drives) will fail the entire array, whereas any two drives failing in a RAID 1 + 0 array will only fail the entire array if the drives concerned are both halves of the same mirrored pair.

    Please tell me if I’m wrong

    Like

  2. To quote “RAID 10 is RAID 0+1″… sorry but that’s rubbish!
    RAID 10 is RAID 1+0!
    RAID 01 is RAID 0+1!
    So to have said “to avoid confusion RAID 0+1 = RAID 01 is called RAID 1+0 = RAID 10″ is incorrect!?
    Surely you’re not claiming that RAID 0+1 is the same as RAID 10?

    Like

  3. I tried to shrink production db it has three files of data. each of them left about 220 MB free space.
    database data_1 size 2192 MB, free space 0 MB.
    data_2 size 2219 mb, free space 224.6 mb
    how can I shrink them?

    DBCC shrink database (proDB, ?)
    Go

    Like

  4. Alan and Dan are correct. Pinaldave and Paul are incorrect. RAID 0+1 is NOT the same as RAID 10. The RAIDs differ because the RAID modes are layered (nested) in the opposite order. RAID 0+1 is vulnerable to single drive failure, as Alan observed correctly. This point is confirmed and reinforced on multiple RAID reference sites.

    Like

  5. I agree with Rich.

    RAID 01 is a mirrored configuration of two striped sets.
    RAID 10 is a stripe across a number of mirrored sets.

    Both will combine the Striping and Mirroring But the Difference is which level applied first.

    Like

  6. Hi, i have been going thru the article on RAID. I found that it is defined as Redundant Array of Inexpensive Disk. While it may sound correct, isn’t the real definition is Redundant Array of Independent Disk?

    Like

  7. You are right, what is commonly called rai10 is actually raid1+0, and raid01 is actually raid0+1.

    But there is a raid10 type in Linux, which is different, it is not a nested raid type, but all are done within the same kernel driver. It has the advantage of doing striping on all constituing drives at sequential reads, with the “far” layout, giving almost the performance of raid0 . It is well suited for SQL operations, as there is also some optimizations in using only the outer faster sectors for reading, and also limiting read seeking to the outer (faster) sectors on the disk..Raid10 can be constructed with only 2 drives.

    More on linux raid incl raid10 can be found on http://linux-raid.osdl.org

    raid5 cab also be very fast, for sequential reading.with an observed performance ov a little over N-1 times the drive nominal speed, N being the number of drives active. This actually also goes for sequential writing, having a write speed of N-1.

    For SQL uses RAID5 is not the best choice, the writing speed is something like N/4 because you need 2 reads and 2 wrtes to change a smaller data block.

    Like

  8. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  9. Yes! RAID-10 is first RAID-1 then RAID-0. RAID-01 (not to be confused with RAID-1) is first RAID-0 then RAID-1. Please do a google search on these terms and spend up to 1/2 hour reading up. No IT professional should ever say that “RAID-10 is 0+1″. Yes in plain arithmetic 0+1=1+0. But this is not the same. Be responsible people! Others are reading you too!

    Like

  10. Pinal,

    This is going to be an interesting case study. We currently use disk mirroring (RAID 1) and are completely unimpressed with our write performance. We are going to be switching to RAID 10 shortly. Look forward to letting you know about our performance increase!

    Like

  11. This is directly from that same article if you look below it:

    RAID Level 10 Explanation Contains an Error

    The RAID Level 10 explanation given here in the MS documentation is actually for Level 0+1. Level 10 is striping the mirrored segments of disks.

    For Example: If you have 4 drives, drive 1 and 2 are mirrored, drive 3 and 4 are mirrored having 2 logical drives, then stripe them. Here you have high fault tolerance and excellent performance because of striping.

    Like

  12. We are implementing a new SQL server with 18 15k drives and two HW RAID controllers.

    In your opinion, which arrangement is preferable:

    Option 1:
    DB on a RAID 0+1 array (3 drives striped mirrored to 3 drives)
    Logs on a RAID 0+1 array (3 drives striped mirrored to 3 drives)
    OS & SQL on a mirrored drive two drive array
    Two hot spares

    OR…

    Option B
    DB on a RAID 6 adg array over 7 drives (two drives can fail)
    Logs on a RAID 6 adg array over 7 drives (two drives can fail)
    OS & SQL on a mirrored drive two drive array

    Option A has fewer spindles in the array but option B has the parity overhead (minimum because of the HW controllers.

    Any thoughts or insight would be welcome.

    Like

    • Hi Alan,

      You will have to give us some more information before we can adequately determine the right choice.
      You will need to show details of transaction counts, write vs read ops etc. Have you considered putting tempdb out on it’s own?

      You may find familiarity/safety with raid6 (or your junior DBA’s/hardware techs). You may find raid10 simple to manage.

      I haven’t used hot spares before, so while it seems to help your recovery rate, I’m always close by my servers. If this is in a datacentre that could be a different story.

      Your answer lies in the comparison between read and write operations, as raid6 will have the double parity penalty.

      Like

  13. Cheng, you should avoid shrinking databases, and if you do, I would bulk copy all tables out and bulk copy them back in to defragment.

    Like

  14. If you are using RAID 10 to store data and log files, is there any benefit to creating multiple .mdf files for large databases to take advantage of SQL’s proportional fill algorithm (since the large .mdf file is striped at the h/w level anyway ?). A lot of our implementations here use a physical device for O/S, paging and apps, and 1 RAID 10 array for data, log and backup files, with this in mind, is there a benefit to having multiple .mdf files for tempdb, as well as user dbs ? (I am aware this is the best practice if you have separate spindles, as we did in the old days).

    Like

  15. I have done some very intresting tests in our infrastructure to identify which the performance of different raid array setup will different disk speed .

    You can find the following details on :

    http://www.databaseskills.net/blogger/raid-setup-comparison-part-1

    http://www.databaseskills.net/blogger/raid-setup-comparison-part-2

    As for my numerous experiences I will tell you this. The setup that fits most of the organizations that I worked with looks as the following :

    OS – RAID 1
    LOG – RAID 1
    TEMPDB – RAID 10 (ideally) ELSE RAID 5
    DATA – RAID 10 (ideally) ELSE RAID 5

    Like

  16. Hi, all

    I got two new server Dell PowerEdge T310 with 4 physical disks configured in a RAID5 array. I want to set up a database mirror schema using this two servers.
    What could be the best file placement strategy (log file, tempdb, and data files) in this scenario?
    Thanks in advance.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s