To be clear, I’m not a disk subsystem storage expert.
Often though, I get requests to suggest how the local or Direct Attached Storage (DAS) should be partitioned for use with a single SQL Server.
The key considerations for storage partitioning, as I see it, are redundancy, performance and capacity.
The below attempts to review the various options for local storage with up to 12 available disks.
Redundancy
The main RAID levels I commonly see are:
| RAID | Key Pros | Key Cons |
| 0 | Fastest overall performance. Allows maximum use of disk capacity. |
All data is lost if one drive experiences an issue. Not recommended for SQL Server, unless other disk subsystem redundancy is also used i.e. Azure Locally Redundant Storage (LDS). |
| 1 | Allows for a single disk failure. | Only 2 disks can be used. Useable disk capacity is halved. |
| 5 | Allows for larger usable disk capacity with storage redundancy. Allows for a single disk failure. |
A minimum of 3 disks is required. Performance significantly reduces if a disk drive experiences an issue. |
| 10 | Allows for a multiple disk failures. Allows for fast IO. |
Useable disk capacity is halved. A minimum of 4 disks is required. An even number of disks is required. |
Performance
To understand the impact of the RAID level on disk IO performance, the following formula has been used:
| RAID | Potential Max Disk IO Formula |
| 0 | (Reads + Writes) / Number of Disks |
| 1 | (Reads + (2*Writes)) / 2 |
| 5 | (Reads + (4*Writes)) / Number of Disks |
| 10 | (Reads + (2 * Writes)) / Number of Disks |
Purely as an example and based on the supplied formula, the below shows the IO work required per disk for 3 different types of IO work loads.
In theory, the lower the disk IO per disk, the better the performance.
Please note that in the real world, it would be preferred to leave 1 or more hot spares to allow for quicker recovery from a disk failure.
Balanced work load – A total of 50 read and 50 write IO requests.
Notice that for this type of work load, a RAID 1 has less IO requirements per disk than a RAID 5 with 3 disks.
| No. of Disks |
RAID 0 |
RAID 1 |
RAID 5 |
RAID 10 |
| 1 | 100 | – | – | – |
| 2 | 50 | 75 | – | – |
| 3 | 33 | – | 83 | – |
| 4 | 25 | – | 63 | 38 |
| 5 | 20 | – | 50 | – | 6 | 17 | – | 42 | 25 |
| 7 | 14 | – | 36 | – |
| 8 | 13 | – | 31 | 19 |
| 9 | 11 | – | 28 | – |
| 10 | 10 | – | 25 | 15 |
| 11 | 9 | – | 23 | – |
| 12 | 8 | – | 21 | 13 |
Higher Read work load – A total of 80 read and 20 write IO requests.
Notice that for this type of work load:
– RAID 10 does not offer much IO improvements over RAID 5 with 4 or more disks.
– RAID 1 has higher IO requirements per disk than RAID 5 with 3 disks.
| No. of Disks |
RAID 0 |
RAID 1 |
RAID 5 |
RAID 10 |
| 1 | 100 | – | – | – |
| 2 | 50 | 60 | – | – |
| 3 | 33 | – | 53 | – |
| 4 | 25 | – | 40 | 30 |
| 5 | 20 | – | 32 | – | 6 | 17 | – | 27 | 20 |
| 7 | 14 | – | 23 | – |
| 8 | 13 | – | 20 | 15 |
| 9 | 11 | – | 18 | – |
| 10 | 10 | – | 16 | 12 |
| 11 | 9 | – | 15 | – |
| 12 | 8 | – | 13 | 10 |
Higher Write work load – A total of 20 read and 80 write IO requests.
Notice that for this type of work load that RAID 5 with 4 disks has lower IO requirements per disk than RAID 1.
| No. of Disks |
RAID 0 |
RAID 1 |
RAID 5 |
RAID 10 |
| 1 | 100 | – | – | – |
| 2 | 50 | 90 | – | – |
| 3 | 33 | – | 113 | – |
| 4 | 25 | – | 85 | 30 |
| 5 | 20 | – | 68 | – | 6 | 17 | – | 57 | 20 |
| 7 | 14 | – | 49 | – |
| 8 | 13 | – | 43 | 15 |
| 9 | 11 | – | 38 | – |
| 10 | 10 | – | 34 | 12 |
| 11 | 9 | – | 31 | – |
| 12 | 8 | – | 28 | 10 |
With the above IOPs per drive known, I suspect the next question would likely be regarding the estimated MB throughput based on the type of disk purchased.
The following table is a rough estimate which shows how this can fluctuate:
| Disk RPM (K) | Approx. IOPs/Sec |
Approx. MB/sec (Using 64KB IO) |
| 7.2 | 75 | 4 |
| 10 | 125 | 7 |
| 15 | 175 | 10 |
| SSD | 6,000 | 375 |
Capacity
The disk capacity you could expect when using the common RAID levels would be:
| RAID | Potential Capacity Formula | Minimum No of Disks |
| 0 | Disk capacity * Number of Disks | 1 |
| 1 | (Disk capacity * Number of Disks) / 2 | 2* |
| 5 | (Disk capacity * Number of Disks) – Capacity of 1 Disk |
3 |
| 10 | (Disk capacity * Number of Disks) / 2 | 4 |
* This is also the maximum number of 2 disks allowed.
An indication of what the RAID level using 300GB disks would mean in terms of potential storage capacity is:
| No. of Disks |
RAID 0 |
RAID 1 |
RAID 5 |
RAID 10 |
| 1 | 300 | – | – | – |
| 2 | 600 | 300 | – | – |
| 3 | 900 | – | 600 | – |
| 4 | 1,200 | – | 900 | 600 |
| 5 | 1,500 | – | 1,200 | – | 6 | 1,800 | – | 1,500 | 900 |
| 7 | 2,100 | – | 1,800 | – |
| 8 | 2,400 | – | 2,100 | 1,200 |
| 9 | 2,700 | – | 2,400 | – |
| 10 | 3,000 | – | 2,700 | 1,500 |
| 11 | 3,300 | – | 3,000 | – |
| 12 | 3,600 | – | 3,300 | 1,800 |