Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

SQL Server – RAID levels overview

24 February,2011 by Tom Collins

SQL server typically implements Level : 0,1 & 5  RAID levels

 RAID 5 – blocks striped & distributed parity. Very widely used strategy  and is a variation of RAID 3 (see below).Striping and parity are used – striping is the data placement across multiple drives. Parity is fault tolerance – it works on maintaining data  on the other disks even if one drive fails.

 Data is striped in large blocks across the array.- this also includes writing parity across the disks . The parity provides the redundancy. Data is arranged in such a way on the disk array, that the 2 information categories are on different disks. Striping with parity is generally better than level 1 – but if a stripe member is missing , performance of reads can decrease- if there is a disk failure.

At minimum 3 drives are needed-  which builds in the capacity for 1 drive to fail – while the parity on the other 2 drives rebuilds the content from the 3rd drive.

On first appearances RAID 5 would not appear to be an adequate RAID configuration for OLTP systems – mainly because of the write penalty. This needs to be weighed up against the cost differences between RAID 10 – due to more disks – and the trend of very large caches which seek to minimise some of the  RAID 5 disadvantages.

 The letters represent different blocks and the p1,p2,p3 represent parity

 DISK 1              DISK 2              DISK 3

 N                      O                      p1

P                      p2                    Q

P3                    R                      S

 RAID 3 – It is worth mentioning RAID 3 – as RAID 5 is a variation of RAID 3.  Three disks as a minimum are required . One of the disks is a dedicated storage for the parity information about the other two disks. If there is a disk failure the information in a disk is recreated from the other two disks.

RAID 3 is not suitable for database storage – as the dedicated single parity disk is  a significant bottleneck. The disk maintaining parity has to absorb a large workload – particularly if write intensive – and increases the CPU overhead.

RAID 0 –  also known as disk striping .Blocks striped , no mirror and no distributed parity. The disk file system is called a striped set . In theory, read\write performance is improved as operations are spread across all disks – in blocks and in a fixed order. This means activity is performed simultaneously and independently. For example , in a 4 drive RAID set ,if  the RAID controller receives an 8 kb block to write – the RAID controller would split the block into even chunks of 2kb and write to all the 4 drives

As only blocks are striped a minimum of 2 disks is required – but no redundancy – therefore is not recommended for Production Tier 1 systems.

 DISK1               DISK 2

 A                      B

C                      D

E                      F

 RAID 1 – (also known as disk mirroring) Blocks mirrored .No stripe and no parity. The disk file system is called a mirror set. The mirroring element is the redundant copy for a given disk. All primary data is also written to the mirror disk. Read performance is improved as the reads occur from all devices therefore lessening contention– but write performance suffers although in theory they are in written in parallel to the devices

Redundancy is good – as the blocks are mirrored – and as there is no parity\striping the performance is good

 DISK1               DISK 2

 A                      A

B                      B

C                      C

 RAID 10  (1 + 0) and RAID 01 (0+1) mirroring and  striping. The difference between RAID 10 and RAID 01 is that in RAID 10 individual disks are mirrored and in RAID 01 the mirror is applied to the striped volume. RAID 10 is a stripe of mirrors and RAID 01 is a mirror of stripes.

 In RAID 10 ,a striped array of disk is used and then mirrored to identical set of mirrored disks. For example , create a 5 disk striped array – which is then mirrored to another 5 striped disks. So , there is the initial striping performance benefit – with the mirrored array redundancy.

 RAID 10 requires as a minimum 4 disks

 In the example below – Disk 1 & Disk 2 are striped – and then Disk 3 & Disk 4 are mirrored from Disk 1 & Disk 2

 DISK1               DISK 2              DISK3               DISK 4

 A                      B                      A                      B

C                      D                      C                      D

E                      F                      E                      F

RAID 6  - extends RAID 5 with the additional parity block. From a database server perspective , there is no cost on the reads , but there is a cost on the write as there are extra parity calculations 


DISK 1              DISK 2              DISK 3              DISK 4              DISK 5  


N                      O                      A                      p1                    p1

P                      C                      p2                    p2                    D                     

G                      p3                    p3                    H                      I



 Making decision as to which RAID level to use – is a serious consideration. A  fully impelemented RAID system can get very costly , very quickly.  Level 0 is the cheapest and Level  10 is the most expensive – but offering differing levels of redundancy and performance.

I’m involved in regular benchmarking  testing for storage performace acceptance levels – and the decision is not as simple as choosing a RAID level.

 There should always be  a thorough  review process over RAID  configurations. Matching IO patterns should be  the basis of the review in identifying the RAID configurations. End-to-end analysis is required – Tuning database, data transfer rate to CPU, hardware, read\write ratio , cache,load distribution,RAID levels.

Read More

SQL Server - How to check Disk Latency - SQL Server DBA

SQL Server - Disk IO performance and SQLIO - SQL Server DBA

IO_completion on disk solved with Wait Stats and Perfmon

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – RAID levels overview | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer