Disk Partition Alignment: It Still Matters--DPA for Windows Server 2012, SQL Server 2012, and SQL Server 2014

  • Article

Introduction

I continue to receive dozens of inquiries each year about this issue.  The “fix” in contemporary versions of Windows Server combined with the absence of formal guidance since the white paper’s publication has led some to believe that disk partition is no longer a best practice.  This is incorrect.  

Partition alignment remains a best practice for all versions of Windows Server as well as SQL Server, including SQL Server 2012 & SQL Server 2014.  No exceptions.  Period.  If, for whatever reason, misaligned volumes are created, they will fail to deliver their expected performance.  SQL Server installed on such volumes will suffer concomitant performance degradation. 

Disk Partition Alignment White Paper

Prior to joining the SQL Server Customer Advisory Team (SQL CAT), I was asked to document my presentation on Disk Partition Alignment as a formal white paper which was published in May 2009. 

Disk Partition Alignment Best Practices for SQL Server
https://technet.microsoft.com/en-us/library/dd758814.aspx

Denny Lee (blog|@dennylee) co-authored the paper with me.  Some of the industry’s best & brightest storage gurus contributed or reviewed it.

Root Cause

A design decision that had been extant in all versions of Windows prior to Windows Server 2008 baked misalignment into the OS; thus it was a ubiquitous issue, resulting in most cases in an enormous performance hit, especially for random read I/O, often resulting in superfluous IOPs of 20%, 30%, up to 50%.  Many customers were unaware of partition alignment.  Even experienced disk administrators were often unfamiliar with it.  Explanations were often initially met with disbelief.  Indeed, when it first came to my attention, I was wide-eyed, struck plumb dumb with what I was being told.  I trusted the source, yet I performed my own experiments to verify.  It turned out to be a well-known issue amongst my colleagues at SQL CAT. 

Fortunately, the root cause was remediated in Windows Server 2008.  All volumes created by versions of Windows since then should by default be aligned; but partitions must be validated—see below.

Best Practices for Contemporary Versions of Windows Server & SQL Server

  • Because of the significant performance hit & the challenges associated with remediation, it’s a best practice to validate disk partition alignment on new volumes on which SQL Server will be installed, especially those from which high performance is expected, especially random read I/O.  This applies to MBR or GPT basic & dynamic disks.
  • There are vendor-specific recommendations.  When consulting with your hardware partners, be certain you do so with personnel whose knowledge on this topic is authoritative.
  • Though partition alignment is done natively by Windows Server 2008 onward, many storage admins nonetheless explicitly configure alignment during volume creation.  Whether you accept the defaults or manually align new partitions, always validate alignment per your vendor recommendations or the correlations defined by the white paper or this blog.
  • Misalignment of existing partitions created on Windows Server 2003 is not remedied simply by attaching them to newer versions of Windows Server.  It is necessary to copy the data to newly created, aligned partitions.
  • The performance impact of misalignment is not as apparent on SSD relative to spinning media.  Yet partition alignment is required for optimal performance.

Summary

Many factors contribute to optimal disk I/O performance. partition alignment properly correlated with stripe unit size and file allocation unit size remains a best practice.  Partition alignment provides an essential & fundamental foundation for optimal performance.  See the white paper or my blog posts for specifics.

Contributors

Thomas Kejser (blog), former SQL CAT PM
Mike Ruthruff, former SQL CAT PM
Mike Anderson (blog), Principal Engineer, Microsoft
Sam Tudorov, Director, Simecom Inc.

References

Disk Partition Alignment Best Practices for SQL Server white paper
My Blog Posts
Windows Disk Alignment, a superb post on Mike Anderson’s SQL Velocity blog
EMC Symmetrix with Microsoft Windows Server 2003 & 2008 Best Practices Planning