HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
understandingpartition2012disksqlandalignmentforwindowsserver

Problem

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:

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?

Solution

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

Context

StackExchange Database Administrators Q#117396, answer score: 2

Revisions (0)

No revisions yet.