Windows Disk Alignment

A quasi authoritative guide to improving the performance of Microsoft SQL Server running on EMC Symmetrix arrays

Overview

While attending the SQL PASS Summit in Seattle this last November, http://www.sqlpass.org/summit/na2010/, I was asked an interesting question. A DBA approached me after attending a session featuring Jimmy May from the Microsoft SQL CAT Team, http://blogs.msdn.com/b/jimmymay/. The DBA knew that he should align his Windows 2003 partitions, but his Storage Administrator stated that the Symmetrix array alleviated the need to perform partition alignment.

His question: "How do I convince the SAN and Windows administrators to align my partitions and improve performance?"

My first thought was shock. I have been dealing with performance issues due to partition alignment since the days of Windows 2000. As I was thinking about how to answer the question I realized that a non-windows administrator could easily come to the conclusion that an advanced disk array would fix anything bad the operating system threw at it.

The simple answer to this loaded question:

• When you create a new partition in Windows Server 2008, the operating system aligns the partition for you.
• If you upgrade Windows to Server 2008 (or R2), or move a partition from an earlier version of Windows, you need to partition align it.
• If you create a new partition using Windows 2000, or Windows 2003, you need to partition align it.

The Problem

Now for the good part; "Mr. Smarty pants blogger, why the heck do I need to do that?" That gentle reader is why we are here today!

There are a ton of great documents from both EMC and Microsoft about the topic of alignment, so I shall not elaborate on the topic, rather; I am going to go over a quick summation and give you the links.

A quick overview of the Symmetrix system;

• Symmetrix uses a Fixed Block Architecture (FBA) when it presents storage to Microsoft Windows. For today's discussion the important notes about FBA are:
  • DMX-2 and earlier systems use a 32K track
  • DMX-3 and later, including V-Max systems, use a 64K track

Windows versions prior to 2008 assume that the physical hard disk uses a 63K track when it presents storage to windows. Windows creates a 63K hidden sector that holds all the information needed to read that partition (including things like the Master Boot Record, and the Disk Signature).

This creates windows NTFS file structures that are not aligned to the underlying V-Max FBA tracks. Assuming a 4K standard allocation unit for the Windows partition, every 16^th 4K I/O will create an extra I/O. This can turn into a huge performance hit when Microsoft SQL server reads 64K extents. Each 64K I/O will cause the storage system to generate an extra I/O request.

The Storage administrator's belief that cache would fix this is not correct because the simple fact is; extra I/O is, well, extra I/O. Each extra I/O sent to the array consumes some amount of compute power, bandwidth, and memory to process the I/O. A properly sized Symmetrix array may not show any performance degradation, but why add extra unnecessary overhead? Taken at scale, many Windows systems, that are all unaligned, will create massive performance degradation.

A Windows Volume that is aligned to the 64K Symmetrix track will not generate the extra I/O. This, ladies and gentlemen, can result in a real cost savings. Jimmy completed a recent Blog entry directly tying business cost to misalignment at scale: http://blogs.msdn.com/b/jimmymay/archive/2009/05/08/disk-partition-alignment-sector-alignment-make-the-case-with-this-template.aspx.

The Solution

For those folks that are not interested in full enlightenment from these great papers listed at the end of the post, use the diskpart.exe cliff notes below to align a new partition:

Note: You should really read these papers, If you break something because you did not read up on the subject, it is not my fault. Really – read them. People worked really hard on them. I WORKED REALLY HARD ON ONE OF THEM!

WARNING: Aligning a Windows Partition is data DESTRUCTIVE. If you have a Windows 2000 or 2003 mis-aligned disk you need to migrate onto an aligned disk. Running Dikspart.exe or Diskpar.exe on a disk with existing data WILL DESTRY THE DATA!

Note: Diskpart.exe is available in Windows Server 2003 SP1 – prior to SP1 Diskpar.exe is used. See the EMC white papers at the end of the post for more information.

This code (taken from Jimmy's white paper) is a huge time saver:

Diskpart

list disk

select disk <DiskNumber>

create partition primary align=<Offset_in_KB>

assign letter=<DriveLetter>

format fs=ntfs unit=64K label="<label>" nowait

First use either the Power Path GUI, or Powermt.exe to map devices from the Symmetrix array to a Windows Disk number:

powermt.exe display dev=all

This command shows that Windows disk 3 is really Symmetrix Device 0057 from the 1111 V-Max array:

Pseudo name=harddisk3

Symmetrix ID=000111111111

Logical device ID=0057

Looking in Diskpart.exe we find, create, offset, and format device 0057 as F$ with an alignment offset of 1024 (that is 1024 blocks or, 64K) and an NTFS allocation unit of 64K (useful for great SQL Server data file performance – don't use this for SQL Server Analysis services volumes. For Analysis Services 4K is a better allocation unit):

C:\>diskpart

Microsoft DiskPart version 6.0.6001

Copyright (C) 1999-2007 Microsoft Corporation.

On computer: MYTESTBOX

DISKPART> list disk

Disk ### Status Size Free Dyn GPT

-------- ---------- ------- ------- --- ---

Disk 0 Online 186 GB 0 B

Disk 1 Online 100 GB 0 B

Disk 2 Online 120 GB 0 B

Disk 3 Online 150 GB 150 GB

DISKPART> select disk 3

Disk 3 is now the selected disk.

DISKPART> create partition primary align=1024

DiskPart succeeded in creating the specified partition.

DISKPART> assign letter=F

DiskPart successfully assigned the drive letter or mount point.

DISKPART> Format FS=ntfs unit=64K label="F$_Data1" nowait

If you need to run this on a lot of disks at the same time you can use the diskpart.exe scripting engine:

Diskpart.exe /s <script>

A working example of a single volume Diskpart.exe script file (this script assumes you are going to format a SQL server data file volume with a 64K NTFS allocation unit):

select disk 3

create partition primary align=1024

assign letter=F

format fs=ntfs unit=64K label=" F$_Data1" nowait

Resources

Please read the following white papers for more information:

• If you are an EMC customer you can search Powerlink.EMC.Com (requires a customer or partner logon to access content):
  • Part number: 300-004-075; Aligning GPT Basic and Dynamic Disks for Microsoft Windows 2003
  • White paper: Using diskpar and diskpart to Align Partitions on Windows Basic and Dynamic Disks
• Anyone can download the Windows server on Symmetrix white paper from (Pages 25 through Page 27 cover alignment):
  • http://www.emc.com/collateral/hardware/white-papers/h6665-symtrx-microsoft-windows-server-wp.pdf
• SQL CAT White Paper on Windows Disk Alignment for SQL Server (note the great technical reviewer <cough>).
  • http://sqlcat.com/whitepapers/archive/2009/05/11/disk-partition-alignment-best-practices-for-sql-server.aspx
  • http://msdn.microsoft.com/en-us/library/dd758814.aspx