SQL Server Techical Article
Published: May 2009Authors: Jimmy May (MS IT Assessment, Consulting, & Engineering (ACE) | bio), Denny LeeContributors: Mike Ruthruff, Robert Smith, Bruce Worthington, Jeff Goldner, Mark Licata, Deborah Jones, Michael Thomassy, Michael Epprecht, Frank McBath, Joseph Sack, Matt Landers, Jason McKittrick, Linchi Shea, Juergen Thomas, Emily Wilson, John Otto, Brent DowlingTechnical Reviewers: Mike Ruthruff, Robert Smith, Bruce Worthington, Emily Wilson, Lindsey Allen, Stuart Ozer, Thomas Kejser, Kun Cheng, Nicholas Dritsas, Paul Mestemaker, Alexei Khalyako, Mike Anderson, Bong KangDisk partition alignment is a powerful tool for improving SQL Server performance. Configuring optimal disk performance is often viewed as much art as science. A best practice that is essential yet often overlooked is disk partition alignment. Windows Server 2008 attempts to align new partitions out-of-the-box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of Windows.
This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions.
The following topics are also included: background information, implementation, vendor considerations, two essential correlations, valid starting partition offsets, and the simple protocol to align partitions, define file allocation unit size, and assign drive letters. It includes results from tests that show how partition alignment can affect performance for SQL Server 2008.
For more information, please refer to the Disk Partition Alignment Best Practices for SQL Server whitepaper.
You can also download a Microsoft Word version of this article.
The SQL Server Customer Advisory Team (SQLCAT) have published a white paper on the topic “Disk Partition
Das SQL Server Customer Advisory Team (SQLCAT) hat ein White Paper zum Thema “Disk Partition Alignment”
A new whitepaper was released yesterday on Disk Alignment, an often overlooked aspect of properly configuring
Excellent whitepaper, been waiting so long for this, now I finally have a whitepaper I can wave at the storage guys to back up my requests for proper alignment.
Pingback from SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA
I've blogged some time ago about Microsoft recommendations for maximizing the performances of your disks
Pingback from Hardware configuration | keyongtech
I have read a lot about this but my disks are 3TB meaning that they are GPT partitions. Do I still need to allign them?
Siz, disk partition alignment is a best practice & is fundamental to leveraging the full performance of your disk I/O subsystem. Alignment affects performance whether your partitions are basic or dynamic, MBR or GPT.
There are plans for enhancing the whitepaper to include updated guidance, validation of existing partitions, & performance info for dynamic disks as well as GPT disks.
Sr. Program Manager
SQL Server Customer Advisory Team: SQL CAT
Jimmy, thanks for your quick response. When I look at the offset (using DISKPART) on my RAID 6 3TB drive (64K striping) I see two partitions 'Partition 1 Reserved 17KB offset', ''Partition 2 Primary 128MB offset'. Using wmic my results are 'Block Size 512, Starting Offset 134235136'. Can you advise what my offset should be in this case when formatting using DISKPART and 64K sectors? Just trying to get my head round this with GPT disks has thrown me a bit.
Thanks again, Siz
Siz, please email me, I have some questions:
Pingback from Partition Alignment and Late Night Infomericals « WebbTech Solutions
Pingback from Brent Ozar’s Reading List for MCM « SQL WARRIOR SPACE
Pingback from @MS_SQL_Server whitepapers New Path to Microsoft Certified Master: Microsoft SQL Server 2008 | sqlmashup
Pingback from New Path to Microsoft Certified Master: Microsoft SQL Server 2008 « SQL Consulting