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?
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.
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.
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:
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.
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: 9/23/2010
About the author
Andy is a SQL Server Developer in the Boston area with 25 years of database and application development experience.
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 - 7:29:41 PM - Andy Novick
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.