(Check out our Pluralsight online training course: SQL Server: Improving Storage Subsystem Performance.)
A while ago I blogged about disk partition alignment, and how the default alignment of 31.5Kb on Windows Server 2003 can lead to enormous I/O performance problems (see Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?). We’ve been on-site with clients this week and that topic came up again. 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: MONKEY
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 I’m doing. 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:
Now – go out to your servers and check the partition alignment – fixing this can give you up to 30-40% I/O performance boost!!
How do you fix it? Well, that’s the downside – fixing it means reformatting the disk to have the correct partition offset or moving the data to a disk that already has the correct partition offset. Remember – although Windows Server 2008 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.
16 thoughts on “Using diskpart and wmic to check disk partition alignment”
paul, what options do you have for fixing this issue? the only one i know of is to build new partitions and transfer your data over. also, i think that new partitions on server 2008 are aligned properly by default, but existing mis-aligned partitions that are upgraded to server 2008 will remain mis-aligned.
I just repaired this without a reformat by using GParted to add 2 megs preceding the partition then rolling it back to 1mb giving me an offset of 1024kb.
For safety and experimentation I did this on a clone of my raid and then cloned it back.
More about this method
How is the best way to fix it?
Do you know if alignment is required for GPT disks? I have not found anything definitive regarding GPT but i have noticed they are different when viewing the offset. So, for example the default starting offset for a gpt disk came up as 131089KB….
@Tom, Pepe The only way to fix these is to reformat to correct the partition offset (or move the data to a new volume with the correct partition offset)
@Andrew I don’t know the answer for GPT disks. SQLCAT is supposed to be putting out some info, but hasn’t yet AFAIK.
Jimmy May also has an excellent cheat sheet that shows you how to check for this using wmic. He also covers checking for block size (also very important for SQL Server performance) using fsutil.
A little Powershell and WMI can return the disk alignment information as well as the logical disk to physical partition links. The wmic and diskpart solutions do not list the logical disks. Also the nice thing about Powershell and wmic is that unlike diskpart these utilities can run remotely. So you could check all of your servers in one pass. See http://goo.gl/0MH7 for more information.
how can I check Windows dynamic disks partition alignment ?
I have a three questions, and I realize the answer depends on many pieces of information, but I’m hoping for a starting point…
The default partition offset for Windows Server 2003 is 31.5 KB and 1 MB for Windows Server 2008. Other than the offset being an even number divisible by 8, do you have any advice on what would be a good partition offset? For example, my transaction logs genereate the following (each on dedicated servers):
publisher 200+ KB/read
distributor just under 1 MB/read
subscriber just under 1 MB/read
Data files are all over the place.
We have multiple databases on each instance except for the distributor. The transaction logs generate anywhere from 25% – 50% of read activity. Is it safe to say transaction log activity is not sequential. I mean if two databases issue checkpoints simultaneously, is transaction log 1 written to sequentially and then transaction log 2 or both at the same time? Does it still matter to segregate data files from transaction log files *if* the tail of the log *is not* necessary?
Lastly, I’m struggling with defining the following for my environments. Since the stripe size is defined by the vendor (128 KB for HP EVA), what would be a good rule of thumb for the following:
– bytes per sector
– should this be 8 KB since a page is 8 KB
– file allocation unit (cluster)
– should this be 64 KB since an extent is 64 KB
– RAID stripe size
– i can provide read/write stats if that helps *and* if you have time…
I have an EMC SAN underlying my SQL. My offset was 32kb on each of the disks. Non of the disks were listed as DYN or GPT.
Does your guidance also apply to SANs? Does diskpart give reliable information when applied to SANs?
Thanks for this post.
@Karol Use dmddiag.exe -v
@Stephen Yup – AFAIK
@Sqlbumper 1) Needs to be a number divisable by your RAID stripe size. RAID stripe size should be 64K at least (my h/w won’t allow less than 128K) 2) checkpoints in different databases are orthogonal. T-log writes are sequential within their files. Separation of log and data is to do with performance and recoverability. In your case, it’s just perf. No way to answrer your Q definitively. 3) Bytes per sector – 512 is fine. Cluster size 64K, stripe size 128K.
Does disk partiton alignment apply to virtual storage such as HP EVAxxxx? There’s limited content on this on the web, and all I received from HP was that it doesn’t apply. I’m going to do some testing this weekend using SQLIO, but I was wondering if anyone out there can confirm this. Also, does DPA only apply to disks that hold sql files (mdf, ndf, and ldf)? Should it be performed for all disks including where the page file resides?
Hi Bob – don’t know anything about virtual storage. Yes, it applies to all volumes that sit on top of RAID arrays, but some are more important than others. I’d be concerned about the page file.
Yes GPT disks need to be correctly aligned. They have the same MBR size as traditional disks.
Yes this applies to virtual storage and thinly provisioned storage. Even more so in these cases as the heads will already be pushed extra hard because the LUNs will be fragmented on the back end.
Has there been any update on the alignment of a GPT drive? I have 7 drives, 2 Fusion IO 320GBs, 5 formatted as MRB (820GB each) and one which is 3.2 TB formatted as GPT. (Don’t really need the space of the larger drive just the spindles)
My offsets for the MRBs are at 1M but the GPT drive get’s defaulted by W2k8R2 to 129M. If nothing has been published on the best practices, would it be best to reduce the size of the logical volume on the array to only present 2TB to the OS to utilize the extra spindles? Or do you think it would be safe to leave the offset at the default and allow the full 3.2 TB to come through to the OS?
Don’t know – I’m not an expert on GPT drives.