Misaligned IOs on SSD storage

Problem

The SQL server error log on one of my secondary replicas is riddled with error messages like this one:

“There have been 271104 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file ….”

image

Analysis

The AOAG is running on Win 2012, and I have been lulled into thinking that drive misalignment was a thing of the past, expelled from reality with my last Win2003R2 server. Seems maybe I was wrong. Although, analysis of the drives show that they are properly aligned. This particular replica is running with local SSD drives, whereas the primary replica is connected to a SAN. I thought “maybe the SAN volumes are misaligned?”, but that to was in order. Turns out the culprit was the SSD drive itself. The SAN volume is using a 512 bytes per sector physical setup, but the new SSD drive is set up to use 4096 bytes.

SSD:

image

SAN:

image

(Mountpoints are in use, thus a path instead of a driveletter.)

This was the only dividing factor I could find.

Even though it seemed futile, I tried to find a way to change bytes Per physical Sector. I mean, the “physical” in the name makes it sound very final. But, by converting the SSD to dynamic, mirroring it and making sure it was aligned I finally got it anyway:

image

I suppose it is still 4096 under the covers, but the cluster has been running for a week, and no new error mesasges have been logged so far.

Solution

Get drives with identical Bytes Per Physical Sector for all your AOAG nodes. I have heard rumors about SANs being able to define this on a per volume basis, but I consider this a poor workaround as it will probably add latency. It sounds better to upgrade all replicas to the new fancy 4096 bytes per sector drives, as they are probably faster for SQL server anyway.

Author: DizzyBadger

SQL Server DBA, Cluster expert, Principal Analyst

3 thoughts on “Misaligned IOs on SSD storage”

  1. Hi
    We have the same problem
    How did you get your 4K volumes down to 512?

    We have 512 on the SAN and 4K for DAS
    Thanks

    1. The key factor in my case was converting the volume to Dynamic disk and alligning the volume, see https://lokna.no/?p=1384.
      If you have an advanced DAS RAID Controller you may be able to change the sector size for the volume from the RAID Controller BIOS. As all such changes, it will probably require deleting and re-creating the volume.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.