SQL Server Disk Partition Alignment

Correcting a bad partition offset requires formatting your disk. You’ve been warned!

The default alignment of 31.5Kb on Windows Server 2003 can lead to enormous I/O performance problems with SQL Server (see Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?). I thought it would be useful to do a quick blog post showing how to use the diskpart and wmic tools. Google them for lots of info from the Microsoft site – but be careful not to play around with any of the destructive options on productions systems. The options I’m using below will not alter the disks in any way.

Note: This stuff applies to MBR disks, not GPT or dynamic disks. Although these require correct alignment too, I don’t have any information on how to do it for those disks. The SQLCAT team will be publishing some guidelines but has not yet done so, AFAIK. Check out the SQLCAT team whitepaper Disk Partition Alignment Best Practices for SQL Server for full details on this topic.
Bring up a command prompt and type diskpart. You’ll see something like:

 Microsoft DiskPart version 6.0.6001
 Copyright (C) 1999-2007 Microsoft Corporation.
 On computer: visualblind

Next you need to list the logical disks that Windows knows about. Type list disk. You’ll see something like:

DISKPART> list disk
 Disk ###  Status      Size     Free     Dyn  Gpt
 ——–  ———-  ——-  ——-  —  —
 Disk 0    Online       136 GB  1434 MB
 Disk 1    Online      1116 GB      0 B
 Disk 2    Online      2036 GB      0 B

Disks 1 and 2 are two RAID arrays I’m using right now for the performance benchmarking series. Notice that the numbers in the Free column aren’t correct – not sure why not.
To see the partitions on a disk, you need to set the diskpart focus to be that disk. Type select disk X, where X is the disk you want to focus on. You’ll see something like:

DISKPART> select disk 1
 Disk 1 is now the selected disk.

And now you can list the partitions on the disk using list partition. You’ll see something like:

DISKPART> list partition
 Partition ###  Type              Size     Offset
 ————-  —————-  ——-  ——-
 Partition 1    Primary           1116 GB  1024 KB

This is the output from one of my Windows Servr 2008 servers, where the default partition offset is 1MB – which doesn’t lead to perf issues.
On another Windows XP system, I get the following output:

 DISKPART> select disk 0
 Disk 0 is now the selected disk.
 DISKPART> list partition
 Partition ###  Type              Size     Offset
 ————-  —————-  ——-  ——-
 Partition 1    Primary            119 GB    32 KB

This disk isn’t aligned correctly. If this was a RAID array, I’d pay a perf penalty every time a read or write straddled a RAID stripe offset. See the blog post link at the top of this post for more details.
Unfortunately, diskpart isn’t always the best tool to use to get partition offsets, as it rounds up the values, and when there are multiple partitions, it can be hard to tell exactly what’s what, especially whtih lots of disks where you need to select each one and then list the partitions.
In this case, use wmic to get the exact numbers. The command is as follows:

wmic partition get BlockSize, StartingOffset, Name, Index

For my server, I get the following output:

BlockSize  Index  Name                   StartingOffset
 512        0      Disk #1, Partition #0  1048576
 512        0      Disk #2, Partition #0  1048576
 512        0      Disk #0, Partition #0  1505755136

For dynamic disks, use:

dmddiag.exe -v
REMEMBER: fixing the partition offset issue means formatting the disk to have the correct partition offset or moving the data to a disk that already has the correct partition offset. Although Windows Server 2008 onward creates disks with the correct offset, taking a disk that was created on Windows Server 2003 and attaching it to Windows Server 2008 will have no effect on the existing partition offset.



Disqus Comments Loading...

Recent Posts

Best Free Public Usenet News Server for NZBGet

If you are looking for the best free public usenet news server in active operation… Read More

February 10, 2020 4:43 pm

Bittorrent IP Blocklists

In addition to using a VPN service, as an extra precaution I've been using the… Read More

October 26, 2019 3:31 pm

FreeNAS Error Creating Pool

command '('gpart', 'create', '-s', 'gpt', '/dev/da8')' returned non-zero exit status 1. If you get this… Read More

June 7, 2019 3:44 pm

Change Grub Default Boot Entry on Linux Mint

I'm dual booting Windows and Linux Mint on my laptop. The grub default is to… Read More

April 23, 2019 7:45 pm

How to Reset Secure Channel On Active Directory Domain Controller

When you're a little too careless about virtualizing your domain controllers, cloning, migrating, backing up… Read More

April 21, 2019 8:14 am

Run Systemd Script Before System Shutdown

I tried to retain the NGINX FastCGI cache and have it persist across system reboots… Read More

April 20, 2019 10:14 am