posts - 219, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Upgrading to SAS drives

We were having some disk drive performance issues at a client recently.  They have a 6 drive array running RAID 10.  We spent some time running SQLIO against the array.  Our standard test was an 8KB random read with a queue depth of eight.  The array was giving us 370 I/O operations per second with a latency of 42 ms.  We cracked open the case and discovered it was using 7200 RPM SATA drives.  We replaced the controller with the same model except that it was designed for SAS (serially attached SCSI) and installed six 15,000 RPM SAS drives.  Running the identical test now resulted in 1,980 I/O operations per second with a 7 ms latency.  Nice! 

Next, we rebuilt the partition using DISKPART.EXE to align the partitions.  (Yes, I know the instructions are for Exchange.  But that's the best description I've found and that's exactly what we did.)  We used a setting of 64 for the alignment.  That improved our performance to 2,142 and 6 ms latency.  Aligning the partitions increased our I/O throughput by just over 8%.

Print | posted on Friday, April 27, 2007 6:45 AM | Filed Under [ SQL Server Stuff ]

Feedback

Gravatar

# re: Upgrading to SAS drives

Nice, thanks for sharing!

I've done SQLIO in lab environment to compare different storage work a few years ago. Nice to see that it is so useful for you in this case. I will keep that in mind in my consulting.

This is the first time I heard about DISKPART.EXE. Learn new stuff everyday :)

Once again, thanks.
4/27/2007 11:37 AM | Ji Village News
Gravatar

# re: Upgrading to SAS drives

Thank you so much for sharing your results - we are in the midst of defining how the same basic approach that Exchange takes to I/O can apply to SQL based applications like MIIS. My current research is around tuning of the Disk Allocation Unit size, RAID stripe size, and disk offset. You've just answered the outstanding question I had on disk offset!!

I am curious to know if you were to reformat the volume with a 64KB block size and run the same tests how much the performance changes.

You can find my current analysis here: http://idchaos.blogspot.com/2007/04/miis-database-sizing-learning-from.html

Thanks you so much and I look forward to collaborating our results.
4/27/2007 6:56 PM | Brad Turner
Gravatar

# re: Upgrading to SAS drives

One additional observation - I've been working on a formula for predicting the I/O throughput (based on the Exchange calculations) and according to your numbers and spindle count: (2142 IOPS / 6 spindles) = 357 IOPS/spindle

The fastest 2.5" SFF SAS drives I've found would clock in around 200 IOPS for a single spindle so I'm trying to ascertain what the missing variables are here. Certainly you've proven that things like the disk offset can improve performance, but this shouldn't be possible beyond the theoretical maximum IOPS output of a single spindle. So, my hunch is that cache is contributing to the inflated score (56% faster) somehow. Can you please describe the model and configuration of the RAID Array controller?
4/28/2007 11:46 AM | Brad Turner
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET