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
Comments
Anonymous
October 22, 2008
The comment has been removedAnonymous
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 removedAnonymous
November 04, 2008
Disk partition alignment is a best practice . Now that SQL Server wait stats are formally documentedAnonymous
November 25, 2008
The comment has been removedAnonymous
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.aspxAnonymous
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 configureAnonymous
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 comAnonymous
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=1251071Anonymous
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 ThanksAnonymous
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 comAnonymous
March 11, 2009
The comment has been removedAnonymous
March 12, 2009
The comment has been removedAnonymous
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.