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!”
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.
5 Comments. Leave new
Hi Brent!
We have been looking at an issue of slow storage for one of our environments. By slow, I mean 55 mb/s of seq(q32T1) writes and 110 mb/s of seq reads (used crystal diskmark). For comparison, our other environments produce ~ 1240 mb/s of seq reads and 250 mb/s of writes.
I’ve read about disk partition alignment best practices for SQL server and I think I understand what it is. We raised a ticket with our vendor and they referenced these 2 links that I think imply how log writes by SQL Server can cause misaligned I/O due to FILE_FLAG_NO_BUFFERING flag used by createFile function:
https://kb.netapp.com/support/index?page=content&id=3013535
https://msdn.microsoft.com/en-us/library/windows/desktop/cc644950%28v=vs.85%29.aspx
Is that possible? If the disk has been set up with proper alignment, is it possible for SQL Server workloads to still cause misalignment?
I apologize in advanced if I have misunderstood anything in the articles.
Thank you!
KL – I’d take a step back there. If CrystalDiskMark is reporting 55 MB/sec of sequential writes, then you have a bigger problem than SQL Server. Have your vendor present you a brand new volume to that SQL Server, don’t put any SQL Server data on it, and then run CrystalDiskMark tests. If you get similar results, then it ain’t a SQL Server problem. Hope that helps!
Thanks for your response Brent!
Btw, what is your take on write-caching for disks (performance). I understand for safety, it is not that recommended unless what’s on cache can persist power failures, but from a performance perspective, are we losing anything by having this feature disabled? It’s just cause right next to the feature, it says it is supposed to help with performance.
Thank you very much!
KL – it depends on the make/model of storage. Your best bet is to check with the guidance from your manufacturer. (We do this kind of guidance for our clients, but getting to specifics is a little beyond what I can do in blog comments.)
Thanks so much!
And I completely understand so no biggie =)