Little SQL Server Tricks: How to Fix Misaligned Log IOs

While installing SQL Server on a new SSD, I run into a problem with the installer. Everything worked, except the add-on features I selected failed. Strange. But then SQL Server did not start, and I found this entry in the Event Viewer:

There have been 256 misaligned log IOs which required falling back 
to synchronous IO. The current IO is on file C:\Program Files\Microsoft SQL 
Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf.

There are a few entries in support forums and a lot of ideas on how to fix that problem. Unfortunately, they did not work and it took me a while to find this post from Microsoft.

The reason for this problem is that I have an NVM Express (NVMe) enabled SSD that offers an optimised block size to read data that is larger than the 4k SQL Server expects:

1
fsutil fsinfo sectorinfo C:

LogicalBytesPerSector :                                 512
PhysicalBytesPerSectorForAtomicity :                    4096
PhysicalBytesPerSectorForPerformance :                  32768
FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096
Device Alignment :                                      Aligned (0x000)
Partition alignment on device :                         Aligned (0x000)
No Seek Penalty
Trim Supported
Not DAX capable
Not Thinly-Provisioned

We can fix this problem with a registry key. To create this entry from the command line, open a PowerShell terminal as an administrator and run this command:

1
New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name "ForcedPhysicalSectorSizeInBytes" -PropertyType MultiString -Force -Value "* 4095"

We now can verify the new value with this command:

1
Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name "ForcedPhysicalSectorSizeInBytes"

Now deinstall SQL Server in Programs and Features (or in Add or Remove Programs in older Windows versions), restart your computer and then install SQL Server once more. This time everything should work and install correctly.