DaveWentzel.com            All Things Data

Alignment

Background

Consider aligning your NTFS volumes with the SAN track sectors. This can be done via the LUN offset on the SAN or through the Resource Kit DISKPAR tool to "sector" align the partition before you format it. What you want is 64 sectors equaling 32,768 bytes. By default, prior to Windows 2008, you will have 63 sectors per track which will result in additional IO when reading blocks 4k and larger. Studies have shown improvements of 10 to 20 percent simply by properly configuring your partitions.  This is applicable to any disk, including DAS.  It is also relevant for Windows Basic and Dynamic Disks and possibly even for 3rd party dynamic disks.  

More Info

SQL Server can show performance improvement if the disk partitions are sector-aligned.  A partition that is sector misaligned will occassionally cause 2 I/O operations when only 1 is physically needed.  Specifically on NTFS the MBR consumes 63 sectors so the partition starts on sector 64 starting offset and the actual partition starts on sector 65.  SAN vendors assume a 64th sector starting offset (but this should always be confirmed with your SAN vendor), this means when an IO hits those "boundary" sectors that it will need to span two disks to perform the IO.  Align the partition on a 4KB boundary from the disk's start to alleviate this.  This is the "math" that should be used to calculate sector alignment:  
64 sectors=32KB aligned=64*512B/sector=32768B
 
How to Check Alignment Quickly
  • MSINFO32/MSINFO64:  Drill down to components, Storage, Disk.  The Partition Starting Offset should be 1,048,576 bytes to be properly aligned.  This is documented inKB929491
  • Diskpar:  Used on Windows 2000, available in the ResKit.  It can still be used in 2003 and later.  It calculates partitions using sectors as the base.  2 switches are used:  (i)nfo, and (s)et and a disk number parameter.  The parameter can be ascertained using diskmgmt.msc. 

diskpar -i 2...is the command to return Disk 2's information.  Much information is returned, but we are concerned with the StartingOffset which is probably 32256.   Let's do the math...32256/4096 = 7.875, which is not an even number hence not sector aligned.  The 4096 is the 4KB boundary mentioned above.  In this case we need to change this. 

diskpar -s 2...to set the correct offset.  This will destroy any data on your partition.  Only run this on a new server.  Now confirm using:

diskpar -i 2...which now returns StartingOffset = 32768. 

Doing the math...32768/4096 = 8 = sector aligned. 

  • Diskpart:  this is new to Windows 2003.  Creattes a partition based on KB, not sectors.  You really shouldn't use diskpart to check existing partitions because it reports in KB, not sectors, means it loses the important precision.  

diskpart

select disk 2

create partition primary align=32

  • dmdiag -v:  None of the tools above work 100% reliably on dynamic disks.  dmdiag -v will probably need to be run first.  Even so, you may still want to consult with your vendor to be sure they don't have a specific tool they like to use.  
Process for New Servers
  • Install the OS and present the SAN volume to the OS for partiitioning.  Sector alignment is not necessary (or even possible) for boot and system partitions on Windows.  This is another good reason not to put your SQL Server files on these partitions.  
  • Do not format the volume yet with NTFS nor install SQL Server.  
  • On Windows 2000 use diskpar.exe (on ResKit) or diskpart.exe on 2003 (also on ResKit).  diskpart may not align sectors correctly on dynamic disks.  
  • See the commands above for diskpar or diskpart.  Diskpart /align is new in Windows 2003 SP1 to help ease the syntax burden.  
  • Now open diskmgmt.msc and format NTFS with 64KB cluster sizes (see below for more).  Do not attempt this process on a formatted drive or a drive with existing data that is not backed up. 

     

Will Microsoft Ever Fix This?

Yes.  This isn't an issue in Windows 2008 anymore.  However, if you upgrade a server and don't rebuild the partition it will still be affected.  

To Make Performance EVEN BETTER

  • The RAID array stripe units should be 64KB so they also align to the partition.  You will need to ask your SAN admin what the stripe unit size is, or for DAS, look in your RAID utility.  The Partition Offset/StripeUnitSize must result in an integer value, meaning they are aligned on boundaries.  
  • The NTFS File Allocation Unit should be set to 64KB, not the default 4KB.  This ensures the NTFS clusters align to the RAID array stripe units and to the underlying partition.   
 

Add new comment