Would you buy a million dollar race car then show up to the track on a clear day with rain tires that aren’t properly inflated and wonder why you’re only doing 180 when the manufacturer says it can do 220?
Your answer was probably close to something like “Of course not!”. Now let’s suppose that your “million dollar race car” is really your DB server and your “tires” are your hard drives. Do you know the right configuration to use to get the best performance out of them? Sure, there’s RAID 1, RAID 10, and RAID 5…but do you know which combination of partition offset, RAID stripe size, and allocation unit size to use?
Reading Physical Database Storage Design on TechNet is a good place to start. With regards to partition offset, there’s been a lot of noise lately about the potentially huge performance gains you can achieve through proper sector alignment. Jimmy May has a 4 part series (Part 1, Part 2, Part 3, Part 4) that’s worth taking the time to read, even if you think you know something-something about it already. Microsoft also has KB Article #929491 which addresses the topic.
I recently had access to hardware that I bet a lot of people are using to run SQL Server on so I ran some tests to find out just how much each variable impacted performance. Today I’m kicking off a series to show what I found. Here’s what I’ve got planned:
- Part 1: Test Harness and Results
- Part 2: Impact of partition offset, RAID stripe size, and allocation unit size on RAID 10 performance
- Part 3: Impact of partition offset, RAID stripe size, and allocation unit size on RAID 5 performance
- Part 4: Impact of partition offset and allocation unit size on RAID 1 performance
- Part 5: RAID 10 vs. RAID 5
- Part 6: RAID 10 vs. RAID 1
- Part 7: Dell PowerVault 220S vs. Dell PowerVault MD1000
- Series Recap
So with that, let’s get started!
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.
8 comments
This is a great series! As an enterprise storage professional, it's nice to see folks in other areas looking this deep into storage questions.
One thing I want to point out, though, is that intelligent caching disk arrays found in many larger data centers (think EMC, IBM, HDS, HP, NetApp, etc) throw these results in the trash however. Each has its own idiosyncrasies, to be sure, but none is as clear-cut as the basic RAID you're experimenting with.
I've had many a SQL DBA argue that he couldn't run on RAID-5 because of his experiences with low-end RAID systems. But after demonstrating the performance of an enterprise array he definitely changed his tune! I'm no cheerleader, just a realist!
Thanks again for your work and posts!
It seems part 2 only loads a blank page :(
Stephen, thanks for your positive comments. You are correct, these tests were performed against local storage and DASD. SANs are a different beast, though from what I understand partition offset still needs to be taken into consideration to achieve optimal performance.
Oh, yeah, block alignment is definitely important in a caching SAN environment. And RAID levels can still have some effect as well, but not anywhere near as much as other factors.
Great piece Kendal. I can see many hours went into it. I was just wondering if you ever got round to testing a configuration with a 128KB stripe size and 128KB partition offset? Given the geometry of partition alignment I would be curious to see the results. Thanks again.
Here's an addition regarding Solid state drives (SSD)
http://sqlblog.com/blogs/joe_chang/archive/2009/07/01/why-have-we-not-seen-tpc-c-and-tpc-e-benchmarks-using-ssd-storage.aspx
Kendal,
Thanks for sharing your methodology and results. Shows the importance of actually benchmarking and comparing possible configurations before you decide what route to take.
Thanks,
Matt
Nice blog yoou have
Post a Comment