We all (should) know that running SQL Server in hyperconverged virtual environments, both on-premises and in the cloud, has some interesting trade-offs. The biggest is write latency from the hyperconverged storage platform underneath the database. We find that write latency suffers compared to traditional all-flash storage, even if the hyperconverged layer is all-flash as well, due to how the hyperconverged layer handles the larger block write that the SQL Server engine drops on it.

If you data environment is on hyperconverged storage, consider using SQL Server trace flag 1800. It takes the larger block size of a transaction log write and drops it to a 4KB block.

This lower block size will help the storage layer slice and dice the I/O operation easier and will reduce in lower transactional commit times, as seen in exhibited storage latency within the OS and with disk stall metrics from the SQL Server engine on a given transaction log file.

My friend Oleg Ulyanov from VMware has a writeup on this change that is titled for SQL Server on VMware on AWS, but it applies to anything hyperconverged, both on-prem and cloud-based. Check it out and test to see if it helps your databases operate faster!