By: Andy Novick | 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:
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.
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.
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 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.
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:
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.
About the author
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
