Disk Partition Alignment

I started here nearly 3 years ago.  Around that time, I also began to attend the Kansas City SQL Pass meetings.  The president at that time had informal chit-chat sessions starting those meetings and asked the question "Who knows if their organization has disk partition alignment?"  A few meetings later, they had an in depth presentation on it and while I've never touched SAN configuration, my key take aways were the default partitioning for Windows 2003 and earlier was most likely incorrect for a given SAN and that misalignment could result in a 30% decrease in read/writes as well as increase disk usage.  A hallway conversation with a DBA indicated that was not an issue in our organization.  

Fast-forward to yesterday's twitter-ings.  Someone had linked Paul Randal's post about how to check partition alignment.  Diagnosing the problem is so easy, even a developer could do it!  And this developer did and the results were unexpected.

H:\>diskpart

Microsoft DiskPart version 5.2.3790.3959

Copyright (C) 1999-2001 Microsoft Corporation.

On computer: DEVSQL0

DISKPART> list disk

  Disk ###  Status      Size     Free     Dyn  Gpt

  --------  ----------  -------  -------  ---  ---

  Disk 0    Online        15 GB  8033 KB

  Disk 1    Online        25 GB   736 KB   *

  Disk 2    Online       150 GB  1023 KB   *

DISKPART> select disk 2

Disk 2 is now the selected disk.

DISKPART> list partition

  Partition ###  Type              Size     Offset

  -------------  ----------------  -------  -------

  Partition 1    Dynamic Data       150 GB    32 KB

DISKPART> select disk 1

Disk 1 is now the selected disk.

DISKPART> list partition

  Partition ###  Type              Size     Offset

  -------------  ----------------  -------  -------

  Partition 1    Dynamic Data        25 GB    32 KB

DISKPART> exit

Leaving DiskPart...

H:\>wmic partition get BlockSize, StartingOffset, Name, Index

BlockSize  Index  Name                   StartingOffset

512        0      Disk #0, Partition #0  32256

512        0      Disk #1, Partition #0  32256

512        0      Disk #2, Partition #0  32256

That's not so good.  Now, admittedly those are Dynamic disks so the second article by Jimmy May comes into play but my crude divination of that report tells me that server is not going to be aligned.  I didn't have RDC access to the non-production servers but I do have the ability to deploy and run arbitrary SSIS packages so choice usage of "Execute Process Task" and a package later, I had a report on non-production servers.  Verdict:  they all have the same block size.

Lesson learned?  Trust, but verify.  Cleanup on this, while straight forward according to the articles, will not be pleasant.

Reference articles 

http://www.sqlskills.com/BLOGS/PAUL/post/Using-diskpart-to-check-disk-partition-alignment.aspx 

http://www.sqlskills.com/BLOGS/PAUL/post/Are-your-disk-partition-offsets-RAID-stripe-sizes-and-NTFS-allocation-units-set-correctly.aspx 

http://blogs.msdn.com/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx 

http://blogs.msdn.com/jimmymay/archive/2008/12/04/disk-partition-alignment-sector-alignment-for-sql-server-part-4-essentials-cheat-sheet.aspx 

http://msdn.microsoft.com/en-us/library/dd758814.aspx