Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (6)   |   Related Tips: More > Hardware

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

SQL Server performance is dependent on the server resources available and disk performance is probably the most important resource. To maximize disk performance for SQL Server, 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 do I find out the allocation unit size and partition offset for my drives?

Solution

How a disk is prepared does make a difference for performance. This is true of directly attached storage and SAN storage as well. The reason is that SQL Server's physical I/O operations are reads and writes in units of either 64K extents or 8K pages. Those I/O requests make their way through the various I/O subsystems and have to be translated into physical operations against the drives or sets of drives. The number of actual physical I/O operations that it takes to complete SQL Server's request can vary depending on how the disk is prepared.

Windows hasn't always helped. Before Windows 2008, disks were partitioned and aligned by default to make use of every possible byte, a holdover from the 1980's and 90's. Until recently most disks have had 512 byte physical sectors. The Windows Master Boot Record (MBR) takes sixty-three of those 512 byte sectors from the beginning of the disk. Windows starts by creating partitions at the next sector, 31.5K bytes from the beginning of the disk. It uses the last 512 byte sector in the first 32K and whatever it takes, to complete the allocation unit size requested when the drive is formatted. Because of the way controllers and caches behave and the way RAID stripes are set up it is sometimes necessary to issue multiple I/O operations to read or write complete allocation units, which is also known as the cluster size. Skipping one more 512 byte sector to align at 32K would have worked better because the clusters would be better aligned with the way the disk works and the number of I/O operations would be reduced. Microsoft recognized this problem and in Windows 2008 partitions are aligned at 1024K by default, leaving one megabyte behind, resulting in better alignment and fewer I/O operations. Of course, this description is very brief and what happens under the hood depends on the exact combination of controller, drives and RAID configuration as well as disk prep.

The ultimate answer to optimizing drive performance is to benchmark the drives under a load similar to the way SQL Server will use the drives. There are several tools available to do this sort of benchmarking. I use SQLIO, which is available as a free download from Microsoft. IOMeter, which was written by Intel, but is now open source, is another free alternative. Benchmarking with SQLIO will be the subject of another article.

Before the benchmarking can begin you have to know the partition offset and allocation unit size of the drive. Windows doesn't make it easy to find either of these items of information, but they can be found with command line tools which are covered below.

Allocation Unit Size

Let's start with the allocation unit size.

Allocation unit size is set by the FORMAT command and is also called the Cluster size. For NTFS formatted drives, and that's what you should be using with SQL Server, Windows supports sizes of 512, 1024, 2048, 4096, 8192, 16K, 32K and 64K. Cluster size is specified with Format's /A switch. The allocation unit is the smallest amount of space that a file can consume and space conscious IT techs will sometimes use the smallest possible size to get the most files on the disks.. However, when working with SQL Server the number of files is very small so this should not be a consideration. When /A is not specified on the FORMAT command, Windows will use a default allocation unit size of 4096 bytes.

The allocation unit size can be located using the fsutil command that is built into Windows. fsutil provides all of the data that you'll ever want to know about a Windows drive. It has many sub-commands, each returning a specialized set of information. To get the allocation unit size, use the "fsinfo ntfsinfo" sub command as depicted in the screen shot below, which was taken from a drive that I was preparing. The red arrow points to the "Bytes Per Cluster", which is the allocation unit size. In this case the default of 4096.

Allocation unit size is set by the FORMAT command and is also called the Cluster size

Partition Offset

Partitions can be created with either the Disk Management plug-in to Windows Management Console (MMC) or with the command line program DISKPART, which comes with Windows 2003 and above. For Windows 2000, there is a predecessor utility, DISKPAR, that is part of the Windows 2000 Resource Kit. Note that there is an update to DISKPART for Windows 2003 that can be obtained at this page. DISKPART has more options than the MMC plug-in, so that's what I'll use here. Unfortunately, DISKPART isn't the best tool to retrieve the partition offset, because it rounds the offset up to the nearest kilobyte giving a misleading answer. Here's DISKPART on Disk 1, Partition 1.

Partitions can be created with either the Disk Management plug-in to Windows Management Console (MMC) or with the command line program DISKPART

The offset is listed at 32 KB, which would probably be pretty good. The problem is that the actual offset is slightly different.

The right tool for retrieving the partition offset is Windows Management Instrumentation (WMI) through the WMIC command line tool. The complete query to run is "wmic partition get BlockSize, StartingOffset, Name, Index". When you use it for the first time it may give you a message about how it's installing WMIC. Don't worry about the message, that's normal. Running the query is depicted here:

The right tool for retrieving the partition offset is Windows Management Instrumentation

Unfortunately Windows utilities aren't consistent in the way they number Partitions. WMIC above numbers from 0 and DISKPART numbers from 1. It's something we'll have to live with, but something to keep in mind.

As you can see, the offset of Disk 1, Partition 0 is 32,258 bytes. That's sixty-three 512 byte blocks. 32K would be 512 bytes past that, which is 32,768 bytes. The partition is misaligned and extra I/O's are required to satisfy typical SQL Server requests. Something ought to be done about it! In future tips we will cover what should be done.

Next Steps
  • Verify the allocation unit, or cluster size, of your disks with fsutil and keep that around.
  • Obtain the partition offset of your drives with the WMIC command.
  • Benchmark your drives to obtain metrics use to evaluate partition offset and formatting alternatives.
  • Consider repartitioning and reformatting your drives to improve performance.


Last Update:


signup button

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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, May 21, 2014 - 2:16:49 AM - Irene Back To Top

WE have the Dell Compellent storage here. I can see the allocation unit is still 4096 with running the fsutil command as suggested. I just wnat to confirm is the fsutil command output also applies to Dell Compellent storage?

Thanks & regards,

IRene


Friday, September 24, 2010 - 3:02:33 AM - pmasl Back To Top
Hello,

I didn't mean for you to disclose upcoming content. Sorry.

I look forward to see the data you collected in your experiments!

Regards

PL


Thursday, September 23, 2010 - 7:29:41 PM - Andy Novick Back To Top
Hi.   In general, you're correct.  I think you'll see in the subsequent articles that the alignment that I use is 1024K, the same as Windows 2008.  And, I know we're skipping ahead, but my experimentation has shown that the the allocaiton unit size doesn't make much difference.  At least on my system.  It may on some others.  I generally use 8K or 64K.  The newest disks are coming with 4K sectors, which make that the minimum.

Regards,
Andy


Thursday, September 23, 2010 - 6:28:42 PM - pmasl Back To Top
There are a few misleading informations in your post, and i humbly suggest you start by reading "Predeployment I/O Best Practices" for SQL Server available here: http://technet.microsoft.com/en-us/library/cc966412.aspx
Generally the volume alignment should be at least 64kb and not 32kb and this has a reason behind it.

Partitions may contain hidden sectors and the total length of these may not coincide with a full track. For example, there are 63 hidden sectors in the MBR (Master Boot Record) so the first block of user data is written across the first and second tracks starting in the 64th sector, misaligning the entire partition.

Subsequently, instead of one IOP (I/O operation) to read or write data, two IOPs are required because if the disk is not aligned, every Nth read or write crosses a boundary, and the physical disk must perform two operations. Hence, the 64-KB offset alignment value.As you said, a brand new Windows Server 2008 install is not affected, but if the OS is upgraded in-place from Windows Server 2003 to Windows Server 2008, you may still experience this problem, because reconfiguring the hard disk offset setting requires the partitions to be re-created and reformatted.

Also, when formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb. Be aware however, that using allocation unit sizes greater than 4 KB results in the inability to use NTFS compression on the volume. SQL Server, although it is not recommended that you use this, does support read-only data on compressed volumes.


Thursday, September 23, 2010 - 4:57:00 PM - Chris Williams Back To Top
Awesome article.  Need the fixes ASAP.  Can't wait for the followup(s).  Thanks for putting this out there.  It was/is very helpful.


Thursday, September 23, 2010 - 1:51:15 PM - Rob Mull Back To Top
Great post! Looking forward to the next one on how to correct the offset issue!


Learn more about SQL Server tools