Disk Partition Alignment (Sector Alignment) for SQL Server: Part 3: PASS 2008

  • Article

I've spoken publicly several times this year to crowds large-&-small:

Last week was my first external international conference, the Professional Association for SQL Server 2008 Community Summit.

This is my second summit & first speaking opportunity. The PASS summit is an incredible event. Plan now for next year!  Attending provides the opportunity to meet industry luminaries, network with peers; & perhaps most importantly, learn new stuff & invigorate your career!

My Topic

The topic shouldn’t surprise anyone who knows me. I’m a devotee of everything related to SQL performance, and disk partition alignment is first on the list. As the Johnny Appleseed of this vital disk configuration protocol, I evangelize it wherever I go:

Disk Partition Alignment for SQL Server
Increase I/O Throughput By 10%, 15%, 30%, or More
The Best Kept Secret in SQL Server Performance

Kudos to Kalen

First let me thank my personal heroine, Kalen Delaney, for contributing two—count 'em, two!—of her books. She even autographed them—Inside SQL Server 2005 T-SQL Programming & Inside SQL Server 2005 Query Tuning & Optimization. I started my presentation by shilling for the books & suggesting to the audience that they pay attention--there would be a quiz* at the end to determine who would win them. BTW, did you know that you can already pre-order her SQL Server 2008 Internals? (Note to oft-mentioned lovely bride: If you're reading this, hint-hint .)

The “Competition”

Speaking of the industry giants such as Kalen, I was presenting at the same time as SQL deities such as Kimberly Tripp & Paul Randall, Quest SQL Evangelist Kevin Kline, & SQL CAT member Michael Thomassy. I thought I'd be lucky to get a handful of people. However, the room was packed— SRO! I learned later that people had been turned away & I was asked to do an encore presentation.

Perhaps SQL CAT Mike Ruthruff's recommendation earlier that day helped. He had given the best presentation on SQL Server on SAN which I have ever seen. He's allowing me to post his deck—stay tuned!

Here’s a picture from the event. That little tiny head in the back is yours truly. My new buddy, SQL genius Brent Ozar, shared it with me. More on Brent in a moment.

clip_image002

The previous speaker only gave me three minutes for setup (grr-rr-rr...). Murphy's Law kicked in with regard to challenges with getting PowerPoint to configure the right monitor as primary. Hey, it's PowerPoint—how hard can it be, eh? Yet I had a room full of eager geeks so two minutes after the scheduled start I embraced the challenge by rolling with the discombobulated config.

New Stuff: New Results, New Graphics, & New Info on Dynamic Disks

My latest-&-greatest deck has new! improved! graphics & some brand new information. I incorporated new perf results from the following Microsoft engineers:

  • Joe Sack
  • Nico Jansen (my A.C.E. teammate!)
  • Matt Landers
  • Jason McKittrick

Kudos to these geekly gents for persuading their customers to implement this best practice. Thanks also to colleague Brian Raymer for making sure my deck passed technical muster.

Another new section is on dynamic disks. Thanks to MS engineer Deborah Jones, customer Brent Dowling from the State of South Dakota, & Veritas/Symantec engineer Jay Jameson I can divine the arcane world of dynamic disk starting partition offset.

Feedback

Feedback exceeded expectations. My colleague Joe Sack forwarded me the RSS feed from Quest's Brent Ozar. Brent has a phenomenal knack to blog in real time. He did a great job of characterizing my deck. I'd not seen Brent's blog before, but I was so impressed I'm now a subscriber.

Afterward, there was a robust & lengthy Q-&-A after which Quest Architect Douglass Chrystall, aforementioned Brent Ozar, & I agreed to collaborate on additional work related to the topic.

On Friday MVPs Joe Chang & Linchi Shi, Motricity's Director Paul Lisagor, & EMC's Darren Bieniek discussed the technical details behind the exciting results—20%, 30%, up to 40% increased I/O throughput—we're seeing in the lab & in the field before-&-after disk partition alignment is implemented. Today Linchi cited our discussion in this post: Finding Disk Partition Offsets Made Easy

In a recent post, Ward Pond blogged about some great feedback from a member of his audience: Speaker was clearly the Chuck Norris of SQL. I’m not even a Karate Kid. However, I did get a kick out of these tweets:

Twitter ID: BrentO
New blog post: Jimmy May explains Partition Alignment (#sqlpass) https://tinyurl.com/6bcqos about 1 hour ago
OH: "If these vendors were physicians, they'd be guilty of malpractice." #sqlpass about 2 hours ago
OH: "I used my search engine of choice, which of course doesn't begin with a G...." about 2 hours ago
Twitter ID: sqlinsaneo https://twitter.com/sqlinsaneo 
Sitting in the Jimmy May comedy hour!!! #sqlpass about 3 hours ago 
he's a good presenter #sqlpass about 3 hours ago 
your not alone. Good stuff here about 3 hours ago 
including red and blue pills about 4 hours ago
Jimmy May: holy correlated waitstats. #sqlpass about 4 hours ago 
Huge crowd amassing outside 613. #sqlpass about 4 hours ago

 

*Inside SQL Server 2005 Book-Winning Q-&-A

The questions & answers for winner’s of Kalen’s book were:

Q. What are two of the three components of the "two essential correlations" for disk partition alignment?

A. Two of these were required to win:

  • Partition_Offset
  • File_Allocation_Unit_Size
  • Stripe_Unit_Size

Q. Besides disk partition alignment, what might be the next best kept secret for SQL Server performance?

A. Elevating HBA queue depth from default values.

This post is made with PracticeThis.com plugin for Windows Live Writer