Misaligned Log IOs After Hyper-V Conversion: Cause and Solution

Every once in a while, SQL Server hands you an error message you've never seen before. This post is one of those cases.

The Error

My customer recently converted a physical server to Hyper-V. Shortly after turning everything back on and resuming the logshipping, this started appearing in the SQL Server error log:

There have been 256 misaligned log IOs which required falling back to synchronous IO.
The current IO is on file t:\MSSQL11.NAVISION\MSSQL\DATA\NEWPROD_5.ldf.

And then it kept coming. Every 256 misaligned IOs, SQL Server logs another one. Before a clean diagnoses was made, the count was up to 3,072 and climbing. You can see here, the counter is cumulative since the last SQL Server restart.

Measuring the Impact

The first thing I did was check write latency on that log file. This query scopes sys.dm_io_virtual_file_stats to the T: drive to see actual write latency:

SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    mf.physical_name,
    vfs.num_of_writes,
    vfs.io_stall_write_ms,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs JOIN sys.master_files mf 
  ON vfs.database_id = mf.database_id 
  AND vfs.file_id = mf.file_id
WHERE mf.physical_name LIKE 'T:\%'
ORDER BY avg_write_stall_ms DESC;

Reminder: SQL Server 2012 uses io_stall_write_ms, not io_stall_write. Don't learn that the hard way.

Results:

DB Name	Physical Name	Writes	IO Stall (ms)	Avg Write (ms)
PRODUCTION	t:\MSSQL11.NAVISION\MSSQL\ DATA\NEWPROD_5.ldf	131,079	2,009,293	15

15ms average write latency on the transaction log. Healthy is under 5ms. Not catastrophic, but real, measurable, and directly in the transaction commit path.

What Is Actually Happening

SQL Server writes to the transaction log using async I/O. For async I/O to work, the write buffer must be aligned to the physical sector boundaries of the underlying storage. When that alignment is off, SQL Server can't use async I/O and falls back to synchronous I/O instead — meaning each log write must fully complete before the next one begins. Every transaction commit waits. This is not normal behaviour.

The question is: why did the alignment break?

Diagnosing the Cause

Storage was the obvious suspect after a Hyper-V conversion, so I started there. Partition offset misalignment is a known gotcha in these types of migrations, so that was my first stop. Run this on the VM from an elevated command prompt:

fsutil fsinfo sectorinfo T:

Here is what came back:

Two things stand out immediately.

First: Partition alignment on device: Aligned (0x000). The partition is properly aligned - so my offset misalignment theory is off the table.

Second, and this is the actual problem: LogicalBytesPerSector is 512, but PhysicalBytesPerSectorForAtomicity is 4096. The logical sector size, what the disk presents to the OS and what SQL Server used when it created the transaction log, is 512 bytes. The physical sector size that the Hyper-V virtual disk actually operates on is 4KB. SQL Server 2012 detects this mismatch on every log write and falls back to synchronous I/O as a safety measure. That is exactly what the error message is reporting.

This is a known consequence of P2V (physical-to-virtual) migrations. The original physical server had 512-byte physical sectors. The Hyper-V virtual disk presents 512-byte logical sectors for compatibility, but the underlying storage operates on 4KB physical sectors. The transaction log file was created in the 512-byte world and is now living in the 4KB world without knowing it.

The Fix: Trace Flag 1800

Microsoft documents this fix in KB3009974, which covers SQL Server 2012 explicitly. The KB title references log shipping, which is exactly what we have here, and the underlying cause is the 512-byte vs 4KB sector size mismatch — produced by the P2V conversion.

The fix is Trace Flag 1800, which tells SQL Server to write transaction log entries using 4K sector alignment going forward, matching what the virtual disk physically requires. Async I/O works correctly again because the writes are now properly aligned. This is not a downgrade — it is correcting the alignment so the intended behavior can resume.

In SQL Server Configuration Manager, navigate to SQL Server Services, right-click the instance, select Properties, go to the Startup Parameters tab, and add:

-T1800

Click Add, then OK. Restart the SQL Server service. A brief service restart is all that is required — no extended maintenance window, no data file changes — no partition rebuilds.

To verify the flag is active after the restart, run the following command in SQL Server Management Studio:

  
  DBCC TRACESTATUS(1800);

The Short Version

If you converted a physical SQL Server to Hyper-V and started seeing misaligned log IO warnings, run fsutil fsinfo sectorinfo against the drive hosting your transaction log. If LogicalBytesPerSector and PhysicalBytesPerSectorForAtomicity do not match, you have a sector size mismatch and this is likely your cause. Add -T1800 as a startup parameter and restart the service.

First time I've seen this one. Hopefully the last.

References

KB3009974 - Fix: Slow synchronization when disks have different sector sizes in SQL Server AG and Logshipping environments