Windows 11 and Windows Server 2022 does not report correct sector information for some storage devices, particularly NVMe M.2 SSD devices. This causes issues with SQL Server IO when reported sector sizes are over 4K.
For your reference, below is example output of the fsutil fsinfo sectorinfo c: command from a working and non-working system:
Working drive:
LogicalBytesPerSector : 512
PhysicalBytesPerSectorForAtomicity : 4096
PhysicalBytesPerSectorForPerformance : 4096
FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096
Problem drive:
LogicalBytesPerSector : 512
PhysicalBytesPerSectorForAtomicity : 16384
PhysicalBytesPerSectorForPerformance : 16384
FileSystemEffectivePhysicalBytesPerSectorForAtomicity : 4096
Does anyone have any recommendations for me to try?
Work-arounds I've seen suggested include:
- Install SQL Server on a drive that reports correct sector information (not over 4K)
- Create a VHD/VHDX and install SQL Server on that drive
- Start SQL Server with trace flag 1800
The trace flag work-around is probably the easiest for your existing installation. However, it doesn't seem LocalDb provides a documented way to specify trace flags (one can use SQL Server Configuration Manager for other editions). I found this answer on SO that shows the registry location for LocalDb startup parameters and tweaked it for SQL 2019 LocalDb and trace flag 1800.
I tested these Powershell commands on my PC and it sets the LocalDB 1800 trace flag correctly.
New-Item -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15E.LOCALDB\MSSQLServer\Parameters' -Force
New-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15E.LOCALDB\MSSQLServer\Parameters' -Name 'SQLArg0' -Value "-T1800" -PropertyType String -Force
You'll need to restart localDb afterwards:
sqllocaldb stop MSSQLLocalDB
sqllocaldb start MSSQLLocalDB