Format drives with correct allocation and offset for maximum SQL Server performance

By:   |   Comments (12)   |   Related: > Hardware


Problem

Disk performance is critical to the performance of SQL Server. Creating partitions with the correct offset and formatting drives with the correct allocation unit size is essential to getting the most out of the drives that you have. I've always been told that the drive's partition offset must be set to 32K and the allocation unit size set to 64K for partitions that hold data and 8K for partitions that hold logs. How does one set these parameters correctly?

Solution

In the article Partition offset and allocation unit size of a disk for SQL Server, I showed how to get both the partition offset and allocation unit size. Allocation unit size is also know as cluster size. In this article I'll show you how to set them according to best practices. The configuration that I'll use is suggested by Microsoft. It's a starting point and each disk system should be tested to verify that optimal performance is achieved. The article Benchmarking SQL Server IO with SQLIO shows how to get started on benchmarking.

The drive that I'm going to be working with is my Disk 1. It's a set of SATA drives directly attached to my server and bound into a hardware (controller) RAID 5 set. Let's take a look at the allocation unit size before changing it:

Disk performance is critical to the performance of SQL Server

Before creating the partition, the original has to be deleted of course, after you've backed up or moved any data. The Computer Management snap-in for MMC can do that or it can be done with the DISKPART tool. Here the DISKPART command is used to delete partition 1 from disk 1. This was the L: drive.

Creating partitions with the correct offset and formatting drives with the correct allocation unit size is essential to getting the most out of the drives that you have

Now it's time to create the new partition. In Windows 2008 Microsoft changed the default partition offset to 1024K. This number is supposed to align well with RAID arrays and SANS. I'll use 1024K instead of 32K.

create the new partition

DISKPART shows the partition at the 1024 KB offset:

DISKPART shows the partition at the 1024 KB offset

However, since DISKPART rounds the offset, the correct tool to use is WMIC, specifically the query "wmic partition get BlockSize, StartingOffset, Name, Index", shown here:

the correct tool to use is WMIC

The value 1048576 is exactly one megabyte and is the proper alignment for most purposes most of the time. Other hardware, such as SANS, might need a different alignment and you'll have to consult the vendor about what's best for their hardware.

Next assign a drive letter to the volume with DISKPART. DISKPART's "list volume" subcommand first shows us the available volumes. The new volume is #1. This is selected and then assigned the letter L.

assign a drive letter to the volume with DISKPART

Finally, format the drive with the desired allocation unit size of 64 kilobytes. Of course, the file system is NTFS. I use DATA as the volume name because L: is going to be a data drive. Here's the output from the Format:

format the drive with the desired allocation unit size of 64 kilobytes

The L: drive now has the offset and formatting that I want. Has the performance improved? Knowing that, is going to take running SQLIO on the drive.

One warning. When you format without the /Q switch Windows zeros the blocks on the drive, which can take a long time. Add the /Q switch if you don't want to wait.

Next Steps
  • Correct the partition offset and allocation unit size of drives where performance might be improved.
  • Repeat the benchmarking process on the drives to verify that the changes have the desired effect.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Andy Novick Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, June 23, 2023 - 4:20:57 AM - halban Back To Top (91332)
https://learn.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-ver16#StorageTypes
Stick to 4kb patition cluster size. all other sizes are not supported.

Wednesday, January 27, 2021 - 9:37:25 AM - Chad Fisette Back To Top (88109)
Sorry, I actually meant the allocation size not the offset, sorry for the confusion.

Wednesday, January 27, 2021 - 9:29:28 AM - Chad Fisette Back To Top (88107)
I'm curious if anyone has tested the 64K vs 2MB offset.

Wednesday, January 27, 2021 - 8:08:22 AM - Andrew Novick Back To Top (88106)
Regarding using 1024 instead of 32K. If you use 32K that's the smallest amount of space that can be allocated and frequent small allocations consume extra space. The article was written quite a long time ago. Today, disks are so large, I'd probably use 32K or 64K.

Tuesday, January 26, 2021 - 5:07:41 PM - Chad Fisette Back To Top (88103)
Did this change for Windows 2019?

Monday, March 9, 2020 - 5:04:27 PM - Yia Back To Top (85035)

You mentioned that you've been told to set the offset to 32K but you decided to use 1024K instead but did not explain why you chose this change?


Monday, January 21, 2019 - 9:24:16 PM - Rakesh Mayatra Back To Top (78834)

 Great Tips on MSSQL.


Thursday, January 12, 2012 - 6:32:01 PM - Tom Masoud Back To Top (15633)

Windows 7 and above do not require partition alignment with AF drives.

A partition is considered aligned when its physical starting address is a multiple of 4096.

But applications must also assure that all writes performed have transfer lengths that are a multiple of 4096 as well. My understanding of SQL is that it may try to perform an update to a data base file that is smaller than 4096. That update will work but it will take much longer for the drive to first read the entire 4096 physical sector involved, edit it in memory, then rwwrite it to the media. This is called Read Modified Write.


Thursday, January 12, 2012 - 6:08:43 PM - Stacey Lake Back To Top (15631)

Isn't MBR offset for disk alignment only required for any OS older than 2008?

 


Tuesday, January 10, 2012 - 9:53:15 AM - Tom Masoud Back To Top (15591)

Advanced format (AF) drives have 512 byte emulation as part of their design. This emulation cannot be disabled. So to the host they look like 512 byte per sector drives but their sector size is really 4096 bytes.

Note Enterprise class drives (SAS, fibre channel, SCSI and some SATA) will remain true 512 byte per sector drives.


Monday, January 9, 2012 - 7:18:33 PM - Andrew Novick Back To Top (15573)

Hi.  I haven't tried this and I'm not sure why you'd want to do 512 byte emulation with SQL Server.  SQL Server is going to access the data in at least 8K byte chunks so there's no need to emulate a smaller size.

HTH
Andy

Andrew Novick

SQL Server MVP
http://www.NovickSoftware.com

 


Monday, January 9, 2012 - 11:07:24 AM - Tom Masoud Back To Top (15557)

Have you performed any comparative performance testing betwen true 512 byte per physical sector drives and new drives that employ 4096 bytes per physical sector with 512 byte emulation?

Thanks

 















get free sql tips
agree to terms