<Note: 20081119:  Deck updated w/ new graphics, perf metrics, & dynamic disk info> 

<Note: 20090225:  Deck updated & moved to skydrive>

<Note: See also Disk Partition Alignment (Sector Alignment) for SQL Server: Part 4: Essentials (Cheat Sheet)>

 

Now that SQL Server wait stats are formally documented & DMVs are available, disk partition alignment may be the best-kept secret related to SQL Server performance optimization.

 

Your mileage may vary, yet in combination with stripe unit size & file allocation unit size, you can increase I/O throughput by 10%, 30%, or more.

 

Sound intriguing?  But wait, there's more!

 

Failure to perform partition alignment may result in significant performance degradation.  Unless performed prior to formatting, partitions created on versions of Windows up to & including Windows Server 2003 are misaligned before the first bit of user data is written.  New partitions on Windows Server 2008 may not be afflicted, yet pre-existing partitions attached to Windows Server 2008 maintain the flawed alignment under which they were created.

 

This deck describes disk partition alignment for SQL Server; documents performance for aligned and non-aligned storage & why non-aligned partitions can be a severe bottleneck; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, & remediation; & it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of pre-existing partitions.

 

The following topics are also included:  background information, implementation, vendor considerations, two essential correlations, valid starting partition offsets, & the simple protocol to align partitions, define file allocation unit size, & assign drive letters.  You may hear the terms partition alignment, disk alignment, volume alignment, track alignment, or sector alignment used synonymously.

 

In spite of Windows Server 2008 out-of-the-box alignment for new partitions, disk partition alignment remains a relevant technology.  Disk partition alignment will remain relevant until Windows Server 2003 is retired & existing partitions are re-built.

 

The information presented here applies to Windows basic disks with master boot record (MBR) partitions.  Details related to GUID partition table (GPT) disks & dynamic disks are not comprehensively addressed.  However, disk partition alignment is a best practice & is required for optimal performance for each of these hard drive configurations:

·         MBR basic

·         MBR dynamic

·         GPT basic

·         GPT dynamic

  

This is the first of a series of posts related to disk partition alignment for SQL Server.  Stay tuned in the coming weeks for additional details, including:

·         Disk Partition Alignment Essentials (cheat sheet)

·         Details related to Dynamic Disks

·         Vendor-specific information, including mitigation of the confusion related to HP EVAs as well as Veritas Enterprise Administrator

 

I invite your questions & insights.

 

A large number of peers & customers have made substantial contributions to this work.  Thank you all for your assistance:

·         Bruce Worthington, Microsoft Windows Principal Development Lead

·         Robert Smith, Microsoft Senior Premier Field Engineer

·         Michael Epprecht, Microsoft Senior Consultant

·         Deborah Jones, Microsoft Windows Senior Development Lead

·         Mark Licata, Microsoft Senior Technology Architect

·         Frank McBath, Microsoft Technical Evangelist, Publisher Computation Press, LLC

·         Steven Wort, Software Development Engineer, Co-author Professional SQL Server 2005 Performance Tuning

·         Jeff Goldner, Microsoft Group Program Manager for Storage

·         Karan Mehra, Microsoft Senior Development Lead

·         Ruud Baars, Microsoft Consultant

·         Clement Yip, Microsoft Senior Consultant

·         Uttam Parui, Microsoft Senior Premier Field Engineer

·         Robert Bogue, Blogger, MVP, Renaissance Man, Author The SharePoint Shepherd's Guide for End Users

·         Vinay Balachandran, EMC Engineer Extraordinaire

·         Joe Chang, SQL Server Performance Guru

·         Joseph Sack, Microsoft Senior PFE, Author SQL Server 2005 T-SQL Recipes

·         Matt Landers, Microsoft Senior Consultant

·         Jason McKittrick, Microsoft Senior Consultant

·         Nico Jansen, Microsoft OE Performance Engineer

·         Anthony Thomas, DBA

·         John Otto, Senior Enterprise Architect, Johnson Outdoors

·         Brent Dowling, Technology Integration Manager, State of South Dakota