Disk Partition Alignment (Sector Alignment): Make the Case: Save Hundreds of Thousands of Dollars
I recently collaborated with Microsoft PFE Daniel Janik to create a template to make the case for disk partition alignment. Perhaps your customers or stakeholders within your organization can benefit. This work was recently broadcast throughout PFE DLs as well as the April 2009 SQLRAP Newsletter. Thanks also to Cindy Gross & Ward Pond for their keen eyes.
This information is available elsewhere on my blog & the Internets. It’s provided here as public service for the first time for convenient access.
Make the Case for Disk Partition Alignment (Sector Alignment)
Introduction
Disk partition alignment is a best practice and must be applied during disk setup. Partition alignment provides a significant increase in system performance. Microsoft engineers have shown again-&-again that disk alignment can improves performance by not merely the nominal 10 – 15% in RAID systems claimed in official documentation, but commonly 20%, 30%, or more.
How to Save Hundreds of Thousands of Dollars
If your SAN cost $1,000,000, & partition alignment provides 10%, 20%, 30%, or more throughput & IOPs at better latency, then alignment arguably provides a direct savings of $100,000, $200,000, $300,000 or more.
Likewise, failure to do so is like throwing that money away.
Look at it another way. Say you have 24 disks on three shelves dedicated to SQL Server. Wouldn't it be great to have the equivalent of an additional shelf of disks? Say you have 200 disks. How different would your life be if you were able to get the performance of the equivalent of 250 disks or more? Imagine the perf gain for hundreds or thousands of disks. As my friend Danielle Nguyen said, we can "make huge perf gains and save huge $$ on additional hardware...especially when a data center has 100s of servers".
Typical Results
Here’re the results of an experiment which have been shown to be typical.
Data was collected for disk latency, duration, and other relevant metrics. The Avg. Disk Transfers/sec counters of the PhysicalDisk and LogicalDisk performance objects were used to measure disk latency. Disk latency is a fundamental measure of disk performance.
The experiment was simple, yet convincing. The results were consistent and significant.
Analysis resulted in the following conclusions:
Disk alignment produced significant improvement compared to nonaligned disks. The measurements document enhancements in excess of 30% for disk latency and duration.
The performance of six aligned disks was comparable to or better than eight nonaligned disks.
The Fundamental Physics
When the file allocation unit (cluster) size is defined at 64KB per best practice yet partition alignment is not performed, multiple IOPs are required to satisfy single requests.
The following diagrams document the phenomena for cluster sizes of 4KB & 64KB. For the latter, each-&-ever I/O request requires two IOPs!
The Remedy
The remedy is simple but has a big gotcha. The good news is that partition alignment is simple to perform; the bad news is that partition alignment must be done at partition creation time, prior to partitions being formatted. This is great if you have a new SAN, but it might be painful to convert large amounts of existing data on misaligned partitions.
Use the command line utility DiskPart to implement alignment when creating new partitions.
Two Essential Correlations; Three Variables: Partition Offset, File Allocation Unit Size, & Stripe Unit Size
There are two correlations which when satisfied are a fundamental precondition for optimal disk I/O performance. The results of the following calculations must result in an integer value:
Partition_Offset ÷ Stripe_Unit_Size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size
Of the two, the first is by far the most important for optimal performance.
Starting Partition Offset
Use this formula to obtain the starting partition offsets for existing partitions:
wmic partition get BlockSize, StartingOffset, Name, Index
File Allocation Unit Size
Run this command for each drive to see the file allocation unit size reported in bytes per cluster.
fsutil fsinfo ntfsinfo c:
fsutil fsinfo ntfsinfo d:
etc...
Stripe Unit Size
The value for stripe unit size must be obtained from your SAN man (or woman).
Dynamic Volumes
Note that the dynamic volumes complicate matters a bit. See this post for more information.
A Common Misalignment Example
The following demonstrates a common misalignment scenario: Given a starting partition offset for 32,256 bytes (31.5 KB) and stripe unit size of 65,536 bytes (64 KB), the result is 0.4921875. This is not an integer; therefore the offset & strip unit size are not correlated. This is consistent with misalignment.
Disk I/O Subsystem Configuration
Configuring optimal disk performance is often viewed as much art as science. Yet an understanding of best practices can result in significant improvements in performance. Some of the many factors which affect disk I/O performance include the number, size, & speed of disks; file allocation unit size; configuration of HBAs & fabric switches; network bandwidth; cache on disk, controllers, & SAN; whether disks are dedicated, shared, or virtualized; RAID level; bus speed; number of paths from disk I/O subsystem to server; driver versions for all components, stripe size, stripe unit size, & workload. Disk partition alignment is the foundation for optimal disk performance. Failure to do so is incompatible with performance & scalability.
References
¡New!
Disk Partition Alignment (Sector Alignment) Best Practices: Characterization, Analysis, and Configuration for Optimal Performance of Windows Disks—Technical Note Series
http://sqlcat.com/whitepapers/archive/2009/05/11/disk-partition-alignment-best-practices-for-sql-server.aspx
Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck
http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx
Disk Partition Alignment (Sector Alignment) for SQL Server: Part 4: Essentials (Cheat Sheet)
http://blogs.msdn.com/jimmymay/archive/2008/12/04/disk-partition-alignment-sector-alignment-for-sql-server-part-4-essentials-cheat-sheet.aspx
An updated version of the Disk Partition tool for Windows Server 2003 is available
http://support.microsoft.com/default.aspx?scid=kb;en-us;923076&sd=rss&spid=3198
Pre-deployment I/O Best Practices (Volume Alignment and NTFS Allocation Unit Size)
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
Disk Subsystem Performance Analysis for Windows
http://www.microsoft.com/whdc/archive/subsys_perf.mspx
Administrivia
<Note: Edited for clarity on 20090510>
<Note: Link to whitepaper added & "Disk I/O Subsystem Configuration" section added>
Jimmy May, MCDBA, MCSE, MCITP: DBA + DB Dev
Senior Performance Consultant: SQL Server
A.C.E.: Assessment, Consulting, & Engineering Services
http://blogs.msdn.com/jimmymay
This post was written with the PracticeThis.com plugin for Windows Live Writer
If it is fast and ugly, they will use it and curse you; if it is slow, they will not use it.
—Computer science professor, billionaire, & entrepreneur David Cheriton
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.”
