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?

  • While this runs via the cmd prompt, you need to lose the spaces to run it from a Powershell prompt (tested on Windows Server 2016): wmic partition get BlockSize,StartingOffset,Name,Index – Razvan Zoitanu Oct 17 '17 at 16:16

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

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

|improve this answer|||||

For what is worth, I only use the 64K allocation unit size when in production or UAT environment and if I have a cluster with a SAN in which I have a LUN for the Data files, another LUN for the LOG files and another LUN for the TEMPDB files, at least.

Other consideration is that if your environment only have one disk and this is the OS disk, leave the allocation unit size as is. If you are trying to setup SQL Server 2014 on a different disk from your OS disk, perhaps in that disk you will get better performance with the 64K allocation unit size, but do consider that a best practice, both for performance and maintenance, states that you separate your data files, log files and TEMPDB files, at best, in different disks. This from a suggestion in this blog post from the SQL Server product group: http://blogs.technet.com/b/dataplatforminsider/archive/2012/12/19/disk-and-file-layout-for-sql-server.aspx

|improve this answer|||||

Check out the SQL Server Setup Checklist from Brent Ozar. Section 4.1 covers drive formatting and block sizes.

SQL Server Setup Checklist from BrentOzar.com

Section 4.1

|improve this answer|||||
  • 2
    Posting a screenshot of that work seems not to have been well-received by the community. Please see How to reference material written by others in the help center. While I believe this to be just about OK in terms of attribution and fair use, you will get a better reception if you follow the advice there to "use their words and ideas to support your own". – Paul White 9 Oct 8 '15 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.