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


By:   |   Updated: 2010-10-11   |   Comments (7)   |   Related: More > 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.


Last Updated: 2010-10-11


get scripts

next tip button



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.

View all my tips
Related Resources





Comments For This Article




Monday, March 09, 2020 - 5:04:27 PM - Yia Back To Top

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

 Great Tips on MSSQL.


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

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

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

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 09, 2012 - 7:18:33 PM - Andrew Novick Back To Top

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 09, 2012 - 11:07:24 AM - Tom Masoud Back To Top

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

 



download


Recommended Reading

Hard Drive Configurations for SQL Server

Benchmarking SQL Server IO with SQLIO

Partition offset and allocation unit size of a disk for SQL Server

How to Monitor Storage Performance with a Single Application

Gather Storage Subsystem Performance Metrics Using the Diskspd Utility for SQL Server Applications





get free sql tips
agree to terms


Learn more about SQL Server tools