SQL Server Performance, Best Practices, & Productivity
<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
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.
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.
@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
Disk partition alignment is a best practice . Now that SQL Server wait stats are formally documented
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)
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.
@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?
Hello everyone! As a first practical post, today I will talk about disk partition alignment to configure
Недавно Кевин Кляйн в очередной раз поднял тему выравнивания размеров кластера и блока, проблему, которая
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.
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
Re HP SAN partition alignment
HP appears to have acknowledged and resolved the issue
Have you any update on when the corruption issue will be sorted? I'm trying to open and geeting the same issue as frank
@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
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!