Misaligned disk partition offsets and SQL Server Performance
I’m starting off this series of posts with a discussion about partition offsets. Diving off at the deep end a bit perhaps, but if your disk setup is not based on firm foundations, you’re setting yourself up for one of the most common and easily-avoidable performance issues right from the off, and a IO performance hit of 20-30%.
The partition offset issue is relevant to any disk partitions created prior to Windows Server 2008. What this means is that whilst your OS may be Windows Server 2008, if the disk you are using was migrated from an older OS the chances are that it may still be exposed to the problem.
Also, certain OEM built machines may be exposed to this problem even on Windows Server 2008 as they often include hidden partitions used for e.g. server recovery date.
Partition offset
Without going into too great a level of detail on how a disk is structured both physically and logically, the partition offset is basically where Windows will start writing its first bytes to disk once the partition concerned is first used.
When a disk partition is created in Windows Server 2003 and before, the first 63 sectors are reserved for the MBR as well as disk vendor proprietary information. That means Windows Server 2003 and before (hereon referred to as just Windows) will write its first bytes of data on the 64th sector.
What’s the big deal?
These 63 sectors are invariably 512 bytes (worth checking with your SAN team/disk vendor however as some SANs may use larger sector size but present the sectors as logical 512 bytes sectors to Windows). Therefore the 64th sector will begin at a 31.5KB sector boundary. What this means is that if this is a SQL Server disk, SQL Server will write its first data page on a 31.5K sector boundary, and every nth page will be written across these boundaries.
The big deal therefore is that each of these nth IO operations will result in an additional IO to read or write the data that was on the page that crossed that boundary (the actual value of n will depend on cluster (sector) size and stripe unit size).
What this means in real (performance) terms is that a properly aligned system will yield a 20-30% performance improvement in IO latency (and therefore query execution time). All this for free, without having to analyse SQL Server configuration, indexing strategy, query plans etc etc.
How do I check my offset?
Checking the offset can be done via a wmi console (wmic) command.
wmic /node: partition get BlockSize, StartingOffset, Name, Index
The above command will show output similar to the following (if the wmic command is being executed locally, there’s no need for the /node parameter):
C:WINDOWSsystem32>wmic /node:myDbServer partition get BlockSize, StartingOffset, Name, Index
BlockSize Index Name StartingOffset 512 0 Disk #0, Partition #0 32256 512 0 Disk #1, Partition #0 32768 512 0 Disk #2, Partition #0 32768 512 0 Disk #3, Partition #0 32256 512 0 Disk #4, Partition #0 32256 512 0 Disk #5, Partition #0 1048576 512 0 Disk #6, Partition #0 1048576
There will be one line of output for each disk and each disk will be identified by its disk number.
Use the Disk Administrator to identify how these drive letters map to your drive letters.
Surprisingly enough, the StartingOffset column is the key column, and will show the starting offset in bytes.
Disks 1 and 2 have 64 sector offset which lines up with e.g. legacy controllers on local disks.
Drives on default values will show a value of 32256, such as disks 0, 3 and 4 in the output above. Divide that by the 512 byte sectors and you’ll get that dreaded value of 63.
Disks 5 and 6 have a 1MB offset which should line up with most modern disk, SAN cache and cluster size scenarios.
What the above output shows is that, depending on the age of the system, there can be a myriad of offsets used which were correct according to how the sytem was configured at the time. Disks 1 and 2 were altered from the default, so at some point in this system’s history someone looked into the issue of partition misalignment and tweaked it for this drive.
So why are disks 3 and 4 on misaligned defaults? Well, have you ever had disks recut to (usually) increase storage. That usually involves blowing away partitions and recreating them. If partition offsetting was not carried out when the partition was created for whatever reason, this is what you can end up with. The newest disks 5 and 6 were created with the latest information to hand, hence the higher offset, but as this slightly contrived example shows (it’s based on real world systems) it pays to be vigilant and review systems that were previously correctly set up.
How do I fix it?
On paper, the fix is very simple; adjust the partition offset. The diskpart command line utility is used to reset the offset. Move the offset along from the default misaligned 31.5K offset to an offset that works with your cluster size and stripe unit size; 64K, or a multiple of 64K usually works on most systems, but where a SAN is involved, it gets more complicated as stripe unit sizes will vary from vendor to vendor and rig to rig, so you’ll have to get your SAN engineer/vendor involved.
Windows Server 2008 defaults to 1024K offset.
Ideally, the offset needs to start at a point where the IO requests that SQL Server issues (which will be a multiple of 8K, depending on the IO request type being fulfilled), the cluster size, and any caching involved at disk controller/SAN level all line up, thus avoiding additional IO requests.
The downside, of course, is the partition will have to be recreated, so downtime will be involved on standalone servers that have no HA solution implemented.
Realigning the partition offset involves blowing away the disk partition in question, so if you’re not familiar with diskpart, leave it to your relevant storage/Windows team to reset the partition. If you really want to do this yourself, you’ll find step-by-step instructions in the SQLCAT team’s Disk Partition Alignment Best Practices article linked to at the bottom of this post.
The resulting 20-30% improvement in latency times have been verified by the SQLCAT team who have published them in that paper on partition alignment mentioned above, and is highly recommended reading for anyone with an interest in this subject.
Links
If you only read one further article on partition offsets, make it this one!
