By: Andy Novick | Comments (6) | Related: > Hardware
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.
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.
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.
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.
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
