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

Tale of Two TechFests

Data Spaces — Thu, 23 Oct 2008 05:33:04 GMT

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

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

contrari4n — Sat, 25 Oct 2008 01:34:38 GMT

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.

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

Jimmy May — Sat, 25 Oct 2008 19:43:54 GMT

@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

Disk Partition Alignment (Sector Alignment): Part I: Slide Deck

ACE Team - Security, Performance & Privacy — Wed, 05 Nov 2008 04:25:39 GMT

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

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

Mani Singh — Tue, 25 Nov 2008 18:11:23 GMT

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.

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

David Ruschinek — Tue, 23 Dec 2008 00:13:56 GMT

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

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

Jimmy May — Tue, 23 Dec 2008 17:40:52 GMT

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

Detecting volume/disk partition alignment issues

Notes from SQL Server Premier Field Engineer — Wed, 24 Dec 2008 07:14:47 GMT

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

Tips for DBA: выравнивание кластеров NTFS и блоков RAID-массивов

Alexander Gladchenko — Thu, 29 Jan 2009 10:32:16 GMT

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

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

Frank — Wed, 04 Feb 2009 02:15:23 GMT

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.

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

Jimmy May — Thu, 05 Feb 2009 05:33:30 GMT

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: Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck

Gerald — Mon, 09 Feb 2009 19:17:41 GMT

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

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

Gaz — Thu, 12 Feb 2009 17:10:15 GMT

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

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

Jimmy May — Sun, 22 Feb 2009 18:44:38 GMT

@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

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

Christian Purnomo — Thu, 12 Mar 2009 06:39:20 GMT

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!

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

Jimmy May — Thu, 12 Mar 2009 15:15:54 GMT

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

Analyzing Storage Performance by Robert Smith on the Counter-of-the-Week (COTW) Blog

Thu, 19 Mar 2009 16:48:24 GMT

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

Disk Partition Alignment (Sector Alignment): Make the Case with this Template

Fri, 08 May 2009 21:15:57 GMT

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