Before attending this session, I’ve always gone to KB article 929491 article on partition alignment. I thought I kinda sorta understood what was going on, but Jimmy’s presentation drew on the best graphical illustrations to show it. Not to mention his audio stylings like, “Holy correlated wait stats!”
Get your binoculars out
Partition alignment is one of those hidden performance tweaks that makes a big (10-20-30%) performance impact. Windows 2008 (and Vista) fixed this, but only for new partitions – if you upgrade an existing Windows 2003 server and don’t blow away the partitions, you’re still affected.
How much will you really be affected? Jimmy’s experiments with unaligned and aligned raid 10 arrays had a 6-disk aligned array outperforming an 8-disk default array. Whoa – that’s a 30% cost savings. He’s a hilarious presenter, which was great because he ended up sounding like a weight loss salesperson.
Partition alignment used to be all about the mechanics of hard drives, but today it’s all about RAID stripe sizes and SAN cache. You can’t just align your partitions at 32kb. While that might have worked in the days of locally attached physical hard drives, it doesn’t line up with common RAID stripe sizes. It even matters for virtual machines, Jimmy says, because the virtual hard drive file lines up with the hard drives on the host.
If your vendor says partition alignment doesn’t matter, you have the wrong contact person at your vendor. I back him up 100% on that. No exceptions.
For basic disks – disk alignment is performed with diskpart, but you can’t use that for reporting alignment. He gave a WMI script to grab the partition offsets, but again, only for basic disks.
For dynamic disks – For Windows dynamic disks, use dmdiag.exe -v (which means verbose). The v is required. For Veritas dynamic disks, use the Veritas tools. He didn’t drill into this in the presentation.
He also touched on the importance of stripe sizes and file allocation unit sizes. He didn’t recommend rebuilding just because of 4kb cluster sizes, but if you’re redoing a partition from scratch, use the size your vendor recommends.
The bad news: you can’t fix any of this online. You have to back up your data, blow away the partitions, align them, and restore the data. But hey, good things come to those who wait.
Jimmy May’s blog on SQL Server storage is a great resource to check out. I hadn’t seen it before, so I subscribed because he kept swearing that he’s going to write more entries about it. I’ve gotta hit this guy up to do video podcasts, because he’s hilarious.
There’s probably some other valuable in there that you might be missing! I cover a lot of easy ways to get more performance out of your SQL Server.
Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.
Website - Twitter - Facebook - More Posts
