patternsqlMinor
Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014
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
I get a starting offset of
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?
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, IndexI get a starting offset of
135266304Some 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
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.