My tale of woe needs a bit of an introduction to the underlying technology. You need to be aware of 2 things: 512 byte vs 4KB format and the (Disk) Alignment concept.

512 byte vs 4KB format

You might not have noticed, but recently some harddisks were quietly introduced that use a new low level format. This format utilizes a 4K sector size, instead of the 512 byte sector size which was used since the dawn of the PC.

The reason for that is simple enough, it uses the surface of traditional storage systems more efficiently, see figure 1 below.

Figure 1 (source Wikipedia)

After reading the article on Wikipedia my first reaction was; “Well…  this looks great, about 12,5% extra usable storage for us simply by (low level) reformatting your harddisk!”.

The Disk Alignment concept

As you might know, disk alignment is an important config item for SQL servers, because misaligned storage stack greatly increases disk i/o overhead.

For those of you who are struggling to remember what the storage stack was about; a quick recap:

1.    The SQL file sits on top of NTFS clusters

2.    The NTFS clusters reside inside (one) of the partition(s)

3.    The partition(s) sits on top of a raid stripeset

4.    The raid stripeset is spread out over multiple harddisks

5.    The data on the harddisk is spread out over disk sectors

Greatly simplified a storage stack looks like this:

Figure 2

A misaligned storage stack looks like this:

Figure 3

The red block pushes all top layers 512 bytes out of alignment with the raid and disk layers. If you now try to read Cluster 1 of the D: partition, you need to read stripe 2 AND stripe 3 of the raid stripeset, costing a 16KB read. Whilst the aligned only had to read 8KB.

For more details on alignment read this article (and its links) by “SQL Man of Mystery” : 

Fundamentals of Storage Systems – Stripe Size, Block Size, and IO Patterns

The SQL Server

The SQL Server config of this tale is a plain vanilla SQL mirror, with one twist: it uses a TMS RamSan-20 server grade solid state disk for all SQL files. This card was chosen because the application that runs on top of SQL can have 600+ connections that ALL cause heavy random reads and writes at the same time. San solutions fail here, simply because of the latency that is inherent to San networks.

The tale of woe

It all started with the morning review of the SQL logs, where a new error drew my attention:

"There have been <x> misaligned log IOs which required falling back to synchronous IO. The current IO is on file <file>"

A quick check of the server logbook showed that the night shift replaced the TMS RamSan (a very fast 450 GB flash storage device) on the mirror server, because it had an intermittent faillure. No problems during the replacement; SQL down, backup files, swap card, restore files, start SQL, do the paperwork.

After re-reading error message, I checked the “books online”, no explanation there whatsoever. Next up was Google; 5 hits, 3 x a list of the all errors SQL can generate and 2 forum post from people like me, who wanted to know more about this error.

Looking at the error message again, the phrase “misaligned log IOs” struck me as its most important part. And sure, I know about disk alignment, but wasn’t that solved in windows / sql 2008+ versions? They should auto align the partitions to the underlying hardware.

A check with the diskpart and fsutil tools confirmed the nightshift made no mistakes when they replaced the card and formatted the disk. For more info on the tools mentioned see: http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

Apart for a small penalty in speed, the mirror seemed to work, so I decided I could take the time to find out what was happening here.

….. time passes …..  the search for the root cause continues…..  many an internet page was scanned for information on this issue….

Finally a tweet to the SQL demi god Paul Randall (http://www.sqlskills.com/blogs/paul/) put me on the right path;

@PaulRandal There have been 1708544 misaligned log IOs which required falling back to synchronous IO. The current IO is on file ... anyID?

@ekelmans You've restored a backup from a 4k drive to a 512byte sector driver on SQL 2005?

All hail to Paul, he was soooo close. And his reply set me on the path to the solution.

As it turns out, the defective RamSan card that was swapped by the nightshift was replaced by a (refurbished) card that had a 4K format, instead of the 512 byte format the original (new) card had. No one noticed it when swapping the card, since the windows format, restore of the drive letter and SQL start all succeeded, and even the alignments were ok.

Then it hit me; The error was caused by the 2 sides of the mirror having different sector sizes.

Somehow the PRIMARY storage engine optimizes its log file structure to the underlying storage stack (cluster and sector size), and since the MIRROR receives identical log entries, but does not have the same underlying structure, the MIRROR reports a "misaligned log IO" error.

I'm sure you must have guessed the obvious solution; adjust either the primary or mirror sector size so that both side match.

Since I upgraded the driver and firmware on the MIRROR to a newer version when I was still in the debugging phase, I decided to adjust / update the primary.

When I started the SQL server on the primary, I got my confirmation that my problem was solved. The SQL log contained the following entry for each logfile:

 The tail of the log for database <dbname> is being rewritten to match the new sector size of <sectorsize> bytes.  <somebytes> bytes at offset <someOffset>in file <somelogfilename> will be written.

There... problem solved...