Disk Performance Hands On: Series Recap

<div><p><em>This is Part 8 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.</em></p> <p>Now that we’ve come to the end of the series let’s look back at what we’ve learned:</p> <p><strong>OLTP Operations</strong></p> <ul> <li>For data files SQL Server reads and writes are 8KB and random in nature </li> <li>For transaction logs SQL Server writes are between 512 bytes & 64KB and sequential in nature </li> </ul> <p><strong>RAID Stripe, Partition Offset, & Allocation Unit Size</strong></p> <ul> <li>Optimal settings for OLTP data operations on local disks and DASD <ul> <li>64 KB RAID stripe </li> <li>64 KB partition offset </li> <li>64 KB allocation unit size </li> </ul> </li> <li>For OLTP transaction log operations on RAID 1 choice of partition offset and allocation unit size make little to no difference in performance </li> <li>64 KB partition offset vs. 32 KB partition offset <ul> <li>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 </li> <li>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 </li> </ul> </li> </ul> <p><strong>RAID 10 vs. RAID 5</strong></p> <ul> <li>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</li> <li>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</li> </ul> <p><strong>RAID 10 vs. RAID 1</strong></p> <ul> <li>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 </li> </ul> <p><strong>PowerVault 220S (SCSI) vs. PowerVault MD1000 (SAS)</strong></p> <ul> <li>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 </li> </ul> <p><strong>Misc Takeaways</strong></p> <ul> <li>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. </li> <li>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. </li> </ul> <p>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.</p> </div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Best Practices Performance SQL Server 2005 SQL Server 2008