Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck

<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

 

DPA Deck Info.txt