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

 

Published 14 October 08 11:57 by Jimmy May
Attachment(s): DPA Deck Info.txt

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# Data Spaces said on October 22, 2008 10:33 PM:

I recently attended the Indy and Tulsa TechFests. Here’s how the two compared.

Both drew about 500 per day (Indy was on a Saturday and Tulsa over was on a Thursday and Friday). Both had great swag. Indy had the better food (a huge Italian lunch buffet!) and Tulsa has the better facilities (there was plenty of room at OSU!).

For those primarily interested in SQL Server, Indy was the place to be. It offered two SQL-related tracks: one for DBAs and one for those interested in BI. Often I wanted to be in both sessions at once.

The best presentation was Jimmy May’s on Disk Partition Alignment. It’s too much to summarize here, but you can read about it on his blog, or better yet, you can catch his presentation next month at PASS.

Tulsa had the basics of SQL Server covered...

Finally, hats off to all the organizers! I had a chance to spend some time with Tulsa’s David Walker and Indy’s Dave Leininger and John Magnabosco. All three were incredible. If any of them were exhausted or frustrated, it didn’t show. What did show was their enthusiasm and passion for excellence. I will definitely be back next year.

http://www.sqlservercentral.com/blogs/data_spaces/archive/2008/10/22/tale-of-two-techfests.aspx

# contrari4n said on October 24, 2008 6:34 PM:

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.

# Jimmy May said on October 25, 2008 12:43 PM:

@Data Spaces:  Thanks for the kind words.  Best presentation--ha-ha! 

I'll look forward to seeing you when I speak at PASS.

@contrari4n:  When I interview folks who claim to be DBAs, I step them through building simple select statements--the majority of my interviewees--no kidding--can't do a join, much less know the difference between an old-style vs. ANSI join.

With regard to interviewing disk partition alignment / sector alignment, if folks who've been doing disks for 20 years haven't heard of it, it's hard to expect newbies to know about it.  ;-)

As the Sr. DB Architect for a VLDB, even *I* hadn't heard of it until I onboarded at Microsoft & caught a whiff of it.  It was a bullet point in a slide.  I was lucky to have realized its importance.  I kept my ears to the ground, my peepers peeled, & I mercilessly interrogated my colleagues, but it took some time to put the whole picture together.  Indeed, even now I'm repeating the process for dynamic disks.  Stay tuned for posts on that topic!

FYI, I include the wmi script for completeness, but I prefer the wmic command:

wmic partition get BlockSize, StartingOffset, Name, Index

I found this in Robert Smith's famous article:  

Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000

http://support.microsoft.com/kb/929491

# ACE Team - Security, Performance & Privacy said on November 4, 2008 8:25 PM:

Disk partition alignment is a best practice . Now that SQL Server wait stats are formally documented

# Mani Singh said on November 25, 2008 10:11 AM:

Great work Jimmy.

Thanks for this wounderful and informative presentation. I first learned about the partition alignments, a couple of years back , but did not pay much efforts into realizing its core, until i read Linchi's Blog.. then your blogs kept my interest alive.. I am a Hyperactive DBA with 13 yrs exp.. this means.. i will do something everyday to my Database servers(break and then re-make my DEV servers) to learn more about them...you Guys help keep that DBA'a neurons Alive daily.(or Life will be boring with DR and Maintenance plans and Monitoring stuffs)

Thanks again.

# David Ruschinek said on December 22, 2008 4:13 PM:

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

# Jimmy May said on December 23, 2008 9:40 AM:

@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?

# Notes from SQL Server Premier Field Engineer said on December 23, 2008 11:14 PM:

Hello everyone! As a first practical post, today I will talk about disk partition alignment to configure

# Alexander Gladchenko said on January 29, 2009 2:32 AM:

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

# Frank said on February 3, 2009 6:15 PM:

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.

# Jimmy May said on February 4, 2009 9:33 PM:

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

# Gerald said on February 9, 2009 11:17 AM:

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

# Gaz said on February 12, 2009 9:10 AM:

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

# Jimmy May said on February 22, 2009 10:44 AM:

@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

# Christian Purnomo said on March 11, 2009 11:39 PM:

Thanks Jimmy, best presentation I would agree with others.

I have been looking around on the net for documentation whether this disk alignment best practice is supported for C:\ - your slides and others that I have seen seem to focus only the DATA drives (i.e. D:\ E:\ and so forth).  would you be able if this is the case?

I'm building a SQL 2005 server on HP P400 smart array with raid 10 + 64kb fas... I guess the D:\ (sql data) and E:\ (transaction log)  are the most heavily used during the uptime of the machine.  the C:\ is not that critical.

Thanks for sharing your thoughts on this issue!

# Jimmy May said on March 12, 2009 8:15 AM:

@Christian: I'm glad you like the material.

Good for you for using RAID10.  We find that RAID 5 implentations remain ubiquitous.

Regarding C:, I think you mean to suggest its performance requirements are very likely not as great as the drives on which your data & log files reside.

Here's the relevant paragraph from the forthcoming white paper for SQL CAT.  Be mindful of the final sentence.

System Drives

System drives in versions of Windows prior to Windows Server 2008 cannot be aligned.  Fortunately, workloads associated with system partitions of dedicated SQL Servers are typically not as sensitive to partition misalignment as disks dedicated to I/O intensive uses, e.g., SQL Server database files from which high-performance is demanded.  As described in the Appendix, modern disks are proprietary “black boxes” and disk partition alignment does not enhance performance for individual disks; however, cache line misalignment may still apply.  Make certain that performance thresholds, particularly disk latency, are not exceeded for all disks, including those on which the OS and SQL Server data & log files reside.

Good luck!

# Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, Productivity, etc.<br><img src="http://img156.imageshack.us/img156/6808/xparentacelogoli1.gif" border="0"/> said on March 19, 2009 9:48 AM:

In my role with MS IT's Assessment, Consulting, &amp; Engineering ( A.C.E. ) team, I work with some of

# Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, Productivity, etc.<br><img src="http://img156.imageshack.us/img156/6808/xparentacelogoli1.gif" border="0"/> said on May 8, 2009 2:15 PM:

I recently collaborated with Microsoft PFE Daniel Janik to create a template to make the case for disk

Leave a Comment

(required) 
(optional)
(required) 

  
Enter Code Here: Required

About Jimmy May

Jimmy May is a Principal Engineer for SQL Server in the Microsoft Assessment. Consulting, & Engineering (A.C.E.) team & is assigned to Team Lee.  He is the Visionary-in-Chief of SQL Server Pros & was formerly the Senior Database Architect for one of the world’s largest, SQL Server high-throughput OLTP VLDBs.  He is a founder & on the executive committees of both the Indiana Windows User Group (www.iwug.net) & Indianapolis Professional Association for SQL Server (www.indypass.org).  He is a recipient of the MS IT Gold Star award, is collaborating with the SQL Server Customer Advisory Team (SQL CAT) on a series of SQL best practices papers, & is a membe of the Microsoft Oracle Center of Excellence.  Jimmy lives in Indianapolis, Indiana with his lovely new bride, Phyllis, & Fannie May the Wonder Dog.  Contact him at jimmymay@microsoft.com. Visit his SQL Server performance & personal productivity blog at http://blogs.msdn.com/jimmymay. “In the late 90’s I made a conscious decision to become a geek, & started working with SQL Server in 1999.  Since then it’s been quite a ride—going from the Help Desk to DBA to Architect to entrepreneur to Microsoft Principal Engineer & consultant.”

Search

This Blog

Syndication

Page view tracker