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>
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
· 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
· Vinay Balachandran, EMC Engineer Extraordinaire
· Joe Chang, SQL Server Performance Guru
· 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