SQL Server Disk Partition Alignment
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
