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:

C:\Users\Administrator>diskpart
 Microsoft DiskPart version 6.0.6001
 Copyright (C) 1999-2007 Microsoft Corporation.
 On computer: visualblind
 DISKPART>

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.
 DISKPART>

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
 DISKPART>

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
 DISKPART>

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.

 

Source:
http://www.sqlskills.com/blogs/paul/using-diskpart-and-wmic-to-check-disk-partition-alignment
https://homerl.github.io/2016/04/20/SSD-partition-alignment/

    Advertisment ad adsense adlogger