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

  • Article

<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

Comments

  • Anonymous
    October 22, 2008
    The comment has been removed

  • Anonymous
    October 24, 2008
    I always have this as a standard interview question. I have just telephone interviewed 20 candidates for a senior DBA position and none of them had heard of partition alignment! Thank you for the slide deck.  Your VB Script for getting the starting offset is particularly useful.

  • Anonymous
    October 25, 2008
    The comment has been removed

  • Anonymous
    November 04, 2008
    Disk partition alignment is a best practice . Now that SQL Server wait stats are formally documented

  • Anonymous
    November 25, 2008
    The comment has been removed

  • Anonymous
    December 22, 2008
    In the DELL and EMC documents they suggested to make a 1024 or 2048 kb non mounted partition at the beginning of each drive. Also Bob Duffy has a post on this issue. http://blogs.msdn.com/boduff/archive/2008/01/26/improving-sql-disk-performance-1-partition-alignment.aspx

  • Anonymous
    December 23, 2008
    @David Ruschinek: Bob Duffy has a LOT of great posts.  However, I believe his reference to diskpart -i is in error.  The 1024 or 2048KB non-mounted partition you cite from EMC & DELL are with regard to disks which will be converted to dynamic volumes, correct?

  • Anonymous
    December 23, 2008
    Hello everyone! As a first practical post, today I will talk about disk partition alignment to configure

  • Anonymous
    January 28, 2009
    Недавно Кевин Кляйн в очередной раз поднял тему выравнивания размеров кластера и блока, проблему, которая

  • Anonymous
    February 03, 2009
    Hello, It seems that the pptx file is corruped somehow.  I cannot open it using Office 2003 with the 2007 conversion pack (work machine) or office 2007 (home machine) Actually, I can open it in 2007, but it tells me that it is corrupted and has to "recover" the data.

  • Anonymous
    February 04, 2009
    Frank, microsoft.com support has been looking at the issue.  Send your email address to me & I'll send the deck to your directly: jimmymay at microsoft dot com

  • Anonymous
    February 09, 2009
    Re HP SAN partition alignment HP appears to have acknowledged and resolved the issue http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=1251071

  • Anonymous
    February 12, 2009
    Hi Have you any update on when the corruption issue will be sorted? I'm trying to open and geeting the same issue as frank Thanks

  • Anonymous
    February 22, 2009
    @Gerald, thanks for the link. @Gaz, MSDN has limitations which I can't get past without shrinking my deck.  I'll post it soon to my skydrive, but in the meantime, simply email me: jimmymay at microsoft dot com

  • Anonymous
    March 11, 2009
    The comment has been removed

  • Anonymous
    March 12, 2009
    The comment has been removed

  • Anonymous
    October 06, 2013
    The skydrive link does not work for me.  Is it valid?

  • Anonymous
    October 13, 2013
    Marty, try this link: http://sdrv.ms/1cMr26k Let me know if you have any questions.