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!