This is Part 8 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.
Now that we’ve come to the end of the series let’s look back at what we’ve learned:
OLTP Operations
- For data files SQL Server reads and writes are 8KB and random in nature
- For transaction logs SQL Server writes are between 512 bytes & 64KB and sequential in nature
RAID Stripe, Partition Offset, & Allocation Unit Size
- Optimal settings for OLTP data operations on local disks and DASD
- 64 KB RAID stripe
- 64 KB partition offset
- 64 KB allocation unit size
- For OLTP transaction log operations on RAID 1 choice of partition offset and allocation unit size make little to no difference in performance
- 64 KB partition offset vs. 32 KB partition offset
- For RAID 10 with a 64 KB RAID stripe and 64 KB allocation unit, using a 64 KB offset resulted in an 11% improvement in IOs/sec and MBs/sec, and a 10% improvement in average latency for 8 KB random reads. For 8 KB random writes using a 64 KB offset resulted in a 10.6% improvement in IOs/sec, MBs/sec, and average latency
- For RAID 5 with a 64 KB RAID stripe and a 64 KB allocation unit, using a 64 KB offset resulted in a 5.7% improvement in IOs/sec and MBs/sec and an 8% improvement in average latency for 8 KB random reads. For 8 KB random writes using a 64 KB offset resulted in a 5.5% improvement in IOs/sec, MBs/sec, and average latency
RAID 10 vs. RAID 5
- For a RAID 5 array using the same number of physical disks as a RAID 10 array, RAID 5 offers 10-15% better performance for 8 KB random reads but a whopping 60-65% worse performance for 8 KB random writes
- To get RAID 10 write performance out of RAID 5 will require a RAID 5 array to use roughly 2x the number of physical disks than are present in the RAID 10 array
RAID 10 vs. RAID 1
- If you take a RAID 10 drive which holds a single OLTP data file and reconfigure the physical drives as multiple RAID 1 drives which each holding a data file of equal size and belonging to the same filegroup, expect to see roughly equivalent performance as the RAID 10 configuration
PowerVault 220S (SCSI) vs. PowerVault MD1000 (SAS)
- For both RAID 10 and RAID 5 on a PowerVault MD1000 expect to see 5-10% better performance for 8 KB random reads and 10-12% better performance for 8 KB random writes than the equivalent configuration on a PowerVault 220S
Misc Takeaways
- Make sure you test against your target workloads. As I was writing this series I initially looked at 64 KB random reads. After I re-evaluated my results using 8 KB random reads I changed my recommendation for the optimal RAID stripe size to use.
- My findings apply to local disks and DASD. If you’re on a SAN, things like partition offset are still applicable but ultimately you should consult your vendor or enterprise storage architect about optimizing disk performance.
I’ve enjoyed writing this series, and I hope that you have likewise enjoyed reading it! If you have any questions, comments, etc. please leave a comment below or feel free to contact me at kendal.vandyke at gmail dot com.
About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
6 comments
I have to dispute the RAID5 tests. I have not tested myself but something has to be wrong. I would gladly take a 10-15% gain for read performance on the data drive for most workloads. However, everywhere else says RAID10, RAID10, RAID10. Could it be the controller, cache, something else?
The increase in read performance when using RAID 5 instead of RAID 10 comes at the HUGE hit RAID 5 takes on write performance (~66%).
I went into great detail on this in part 5 of the series, but I can see how I may have mangled the wording of it in this recap. I will edit to make it clearer.
Kendal, I want to say a big thank you for putting together this fantastic series. I have no doubt it took quite some time to produce and I for one certainly appreciate your efforts.
Yah but say your workload was 95% read\5% write wouldn't RAID 5 make sense?
i guess question is how badly that 5% writing is killing the server.
Excellent work. One very important test is missing though. What about the time of resilvering / disaster recovery? I understand there is an impact on this. With RAID5 paraties have to be calculated, with RAID10 the data has merely to be copied from the working disk on the replacement. And recovery is the sole reason for any RAID (except 0).
Post a Comment