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

VMWare vSphere 6.7 ESXTOP Output Garbled

If your VMWare vSphere 6.x environment command output of esxtop looks like a bunch of garbled gibberish (it's actually CSV… Read More

February 28, 2019 7:39 pm 19:39

How To Run RoboCopy Backup in Parallel

From time to time Windows Admins will surprise you with band-aid and bubble gum scripts, that's entirely expected I think.… Read More

February 28, 2019 12:20 pm 12:20

Windows 10 GodMode - The Ultimate Administrator Shortcut

Have you ever wondered what life might be like if the Windows 10 OS somehow had a single folder that… Read More

February 28, 2019 7:58 am 07:58

Samsung Galaxy S9 G960/G965 Stock ROM Firmware Download

There could be any number of reasons for needing to flash manufacturer stock OEM firmware on a Samsung Galaxy S9… Read More

January 24, 2019 7:42 am 07:42

Output IP Address with ipconfig Findstr Ethernet Adapter

How many times have you entered  ipconfig /all at the command line to return a single IP address, then have to strain… Read More

January 20, 2019 12:39 am 00:39

AWS SES Assistance in Enterprise Market - A Must Read

Designing e-mail solutions on a large scale can be a complex and costly challenge for a business: you need to… Read More

December 25, 2018 4:01 pm 16:01