Sql-server – Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014

disk-structurespartitioningsql serversql server 2014windows-server

I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size.
Now I am stuck using Diskpart to set the perfect offset. As far as I understand I have 63 hidden sectors and to align it with my formatting I can use an offset of 1024k , did I get the right?
Anyway when I list my partition afterwards I get this result:

Disk partition

Using command line wmic partition get BlockSize, StartingOffset, Name, Index

I get a starting offset of 135266304

Some websites/blogs recommend 32K other do recommend 1K for 2012, whom can I trust? Currently I have issues understanding it correctly. May the force (you) enlighten me?

Best Answer

Windows server 2008 and up will have new partitions aligned out of the box.

KB 929491 has documented starting offset of 2,048 sectors (1 megabyte) - which covers most of SANs.

Kendal van Dyke has done some testing here.

The results show that for OLTP databases on a RAID 10 array the optimal configuration is a 64 KB RAID stripe, 64 KB partition offset, and 64 KB allocation unit size.

Just for the sake of future readers, dbatools.io has a function Test-DbaDiskAlignment.ps1

Caveats:
Dynamic drives (or those provisioned via third party software) may or may not have accurate results when polled by any of the built in tools, see your vendor for details. Windows does not have a reliable way to determine stripe unit Sizes. These values are obtained from vendor disk management software or from your SAN administrator. System drives in versions previous to Windows Server 2008 cannot be aligned, but it is generally not recommended to place SQL Server databases on system drives.

Refer: Disk Partition Alignment Best Practices for SQL Server

Related Question