Alignment of disk partitions can have a huge performance impact, particularly for database servers. Incorrect alignment from an out-of-the-box installation could tax a system’s I/O performance as much as 35%, depending on the type of activity (1). Partitioning, and therefore partition alignment, is one of the first things to happen when a server is being built. A mistake in that early step can negatively affect the performance of the I/O subsystem for the life of a production database server. And yet it was not long ago that it seemed nobody in the DBA community had ever heard of partition alignment.


Under older versions of Windows, such as Windows Server 2003, partition alignment did not occur properly with an out-of-the-box installation. This means that those legacy systems could have problems today, if folks were unaware of this issue when the system was built. However, starting with Windows Server 2008, partitions created during Windows media-based installation should be correctly aligned. It is still nonetheless prudent to understand partition alignment, and to know how to identify and remedy alignment problems. Misalignment can come up from image-based system builds, and on non-system partitions or drives, such as on that fancy storage shelf you just got to boost your database I/O, or even on that LUN you just got from the SAN administrator.

I should mention that this article only has information regarding basic disks– for dynamic disks, consult the Microsoft whitepaper under “Further Reading”.

What Is Partition Alignment

Without over-complicating things, the basic idea behind partition alignment is that partitions should start at an even multiple of the media’s physical storage unit size. This way we avoid interacting with more physical units than necessary when we perform disk I/O, because we avoid fractional overlap with additional physical storage units.

The smallest physical unit of storage is a disk sector (which for our purposes we can also call a block). These get grouped into tracks. On top of those physical units, we map partitions, which are a logical construct. Partitions are what show up as drives in Windows.

Because there are variables like sector/block size, the number of blocks in a track, and beyond that, RAID stripe size, and I/O behavior in an RDBMS like Microsoft SQL Server, there is some math involved with making partition alignment determinations. There are other articles out there that focus on the underlying technical details, though, so I won’t be delving into those here. (Do note that the underlying numbers may in fact change down the line, depending on many factors. At that point, those who have done this homework will be at a distinct advantage!)

What you need to know now is that there is a tiny but critical bit of storage that begins a hard drive, which maps all of the locations within that storage. By default in many utilities, our first partition comes immediately after that. This is where problems can come up.

The Alignment Magic Number

My quick version goes something like this: not too long ago, it was advisable to start a Windows partition at the 64KB (as in 66560B) mark. This was due to the space occupied by a disk’s MBR, or master boot record, which did not completely fill the last track it occupied. This caused a partition that started immediately after it, at the 63.5KB mark, to span tracks unnecessarily– that is, unless the admin would align that partition to the next unit of physical storage manually.

More recently, starting at the 1024KB mark is a better policy. This is to allow room for the larger GPT, or GUID partition table (which was created to allow the mapping of physical storage greater than 2TB), even if you don’t (or can’t) currently use it. This means your partition will be offset by that amount from the beginning of the disk, and it will be properly aligned to the underlying physical storage.

This is why we go with an offset of 1024KB, or 1048576B, or a multiple thereof, to align our partitions.

Taking a Look at Existing Partitions

The key here is to use a tool that displays everything in bytes. Many tools will round to the nearest kilobyte for whatever reason. In this situation, we need to know the actual state of affairs– without any adulteration or unwanted approximations.

The best way to check on your existing partition layout is by querying WMI with something like “wmic partition get BlockSize, StartingOffset, Name, Index”:

	C:\>wmic partition get BlockSize, StartingOffset, Name, Index
	BlockSize  Index  Name                   StartingOffset
	512        0      Disk #0, Partition #0  1048576
	512        1      Disk #0, Partition #1  107375230976

In the past I have also used DiskExt, from the Sysinternals Suite of tools at Microsoft. (If you haven’t heard of the Sysinternals Suite by Mark Russinovich, take some time to check it out, your career could depend on it someday!)

	C:\Windows\system32>diskext c
	
	Disk Extent Dumper v1.1
	Copyright (C) 2001-2007 Mark Russinovich
	Sysinternals - www.sysinternals.com
	
	Volume: C:
	   Mounted at: c:\
	   Extent [1]:
	       Disk:   0
	       Offset: 1048576
       Length: 107374182400

Doing some quick math, it looks like we are offset by exactly 1024KB. As a DBA, this is the offset I use. Note that Length number also: that is going to signify the starting point for subsequent partitions, if we have any more on the same drive. If that is divisible by 64KB, you will be good to go on the next partition as well. Making your first partition, and all subsequent partitions, an even multiple of 1024KB will achieve this.

If you got something like 63.5KB offset, read on!

DiskPart – The Fixer-Upper

DiskPart is the tool we use to correctly set up partitions for a Windows box. We do so right from the beginning for Windows system builds– this prevents getting the wrong offset in the first place. If you find the wrong offset on a machine already in use, the solution is more complicated: basically, you need to fix the partition that everything else sits on top of, which I also cover below.

Normally, as part of a system build, we use DiskPart in a Windows Preinstallation Environment (PE)(2) prior to installing anything. The Windows PE boot disk is something we make from the Windows ADK (“Assessment and Deployment Kit”, the successor to the AIK, the “Automated Installation Kit”). We use the DISM tool (“Deployment Image Servicing and Management”, the successor to ImageX.exe) to image partitions we install to from installation media, for backup and future install purposes (3). When building up systems, or adding storage for that matter, knowing your partitions is pretty fundamental, as everything else is predicated by it. Note that a partition is where one applies an image one creates, and so image-based builds is a case where one is not assisted by installation media partitioning.

If you are just adding storage, you can run DiskPart from a Command Prompt within Windows. Note that in UAC environments it requires Administrative permission.

When it comes to getting a new LUN from a SAN administrator, there are more layers to the equation. In my opinion, the best approach is to discuss partition alignment with your SAN admin. In my personal experience, some SAN administrators can tell you just what you need to know, while others I have worked with have been surprisingly unaware of partition alignment. In such a situation, keep your chin up: you can help each other out by working through the question together.

What if we were offset by 63.5KB in the previous example? Well, to put it simply, we could get better I/O performance for our server by correctly aligning that partition. To correct an existing mis-aligned system drive basically requires rebuilding the system (there are 3rd-party tools that can perform such feats of daring, but I have had mixed results with them, and do not recommend the approach). Before you bemoan more lost sleep for late-night maintenance work, remember that the effort could yield you as much as 35% improvement in I/O performance (1), and as we all know, I/O is DBA gold. Depending on your uptime requirements, you might just be able to image existing partitions in DISM, and then apply the image(s) to the aligned partitions on a spare machine. A couple of notes for such an undertaking: 1) Do not underestimate how long capturing an image can take– it is best done to a fast local drive (NOT a USB 2.0 external drive, for example); and 2) It probably goes without saying for anyone reading this blog, but I’ll say it anyways: be careful not to rely on a single copy of existing partitions when you tackle a misalignment situation. You do not want to be caught with a corrupt image and no other copy of your data!

DiskPart itself is relatively simple to use. First, you list out your disk(s) with the command “list disk”:

	Microsoft DiskPart version 6.2.9200
	
	Copyright (C) 1999-2012 Microsoft Corporation.
	On computer: FTERRY
	
	DISKPART> list disk
	
	  Disk ###  Status         Size     Free     Dyn  Gpt
	  --------  -------------  -------  -------  ---  ---
	  Disk 0    Online          931 GB      0 B
	  Disk 1    Online         1862 GB      0 B

Then, you point it at the disk you want to partition with “select disk N”:

	DISKPART> select disk 0

	Disk 0 is now the selected disk.

Then, you check to see if there are any existing partitions on that disk by running “list partition”. (If there are, you need to be SURE you know what you are doing. You might delete all existing data on existing partition(s)!)

On a disk that has no partitions, you will presumably be creating the first primary partition, such as for your system installation drive. Here, you should know how large you want your partition to be (say, 100GB– although even that seems to be getting too small for Windows Server installations in today’s day and age!). You will do this with the command “create partition primary”:

	DISKPART> create partition primary size = 102400 align = 1024

Note that “size” is in MB and “align” is in KB.

Now, when you run “list partition”, you should see your new partition:

	DISKPART> list partition

	  Partition ###  Type              Size     Offset
	  -------------  ----------------  -------  -------
	  Partition 1    Primary            100 GB  1024 KB	

You can create another partition for the rest of the disk with this command:

	DISKPART> create partition primary

In our example here, this second partition will be correctly-aligned, as the first partition that it immediately follows is correctly-aligned.

At any point in DiskPart, you can get help on a command by typing “help” before it, like

	DISKPART> help create partition
	DISKPART> help create partition primary

Conclusion

Well, that just about wraps up this topic. Hopefully you’ve found the information useful. It can be surprising how much work it can be to stay on top of something as fundamental as disk partition alignment for your database server, but your reward of better performance from the same hardware is like money in the bank. Perhaps you can put off that next upgrade all that much longer based on the extra performance you can get from this relatively simple setup parameter.

If you have any questions, advice as to better ways to do this, or corrections, please do not be shy, we would love to hear from you.

References

(1) Performance impact of partition misalignment, from Linchi Shea: http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx
(2) Windows PE boot disc creation instructions: http://technet.microsoft.com/en-us/library/dn293200.aspx
(3) Capturing partition images with DISM: http://technet.microsoft.com/en-us/library/hh825072.aspx

Further Reading

Disk Partition Alignment Best Practices for SQL Server: http://msdn.microsoft.com/en-us/library/dd758814.aspx
Jimmy May (one of the authors of the previous article) has some more empirical analysis on his blog: http://blogs.msdn.com/b/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx
Sysinternals: http://technet.microsoft.com/en-us/sysinternals
http://en.wikipedia.org/wiki/Track_partition_alignment
http://en.wikipedia.org/wiki/GUID_Partition_Table

Leave a Reply

Your email address will not be published. Required fields are marked *