SQL Server Trace Flag 1800 transaction log volume input/output (I/O) alignment

When misaligned I/O occurs, an application may experience elevated write latency for transaction log writes during periods of large data insertion into the database.
A detailed example of detecting the potential misalignment can be found in this Broadcom blog. Leverage "Process Monitor" on SQL server side or vSphere tools such as "vSAN I/O Insight" to help diagnose the problem.

The impact of I/O misalignment depends heavily on the workload pattern. In general, for larger sequential write I/O the impact has been observed more significant.

A latency example from a write performance perspective without the Trace Flag 1800:

With the Trace Flag 1800 set:
 

vSphere ESXi exposes VMware Virtual Machine Disks (VMDKs) to the Guest OS with the physical sector size of 512 bytes (512n formatting).
SQL Server uses an advanced mechanism to calculate the transaction log I/O block size. SQL takes the physical sector size of the presented VMDK where the transaction log files are placed to align the transaction log I/O block size. This is different for data volumes which are traditionally using the file system (NTFS) allocation unit size (user-controlled).

I/O disk alignment ensures that data is written to storage media in a way that aligns with the physical sectors of the disk. This helps to prevent misaligned I/Os, which can impact write performance.

By using the trace flag 1800, you can ensure that your SQL transaction log volume is properly aligned with a 4k boundary. Depending on the volume block size of the underlying storage solution, this optimizes write performance.

Before enabling the trace flag 1800, consult with your Microsoft SQL team to confirm potential misalignment and configuration guidance.

Microsoft references the Trace Flag 1800 for Availability Groups with replication groups on different disks. The Trace Flag has proven to be effective in general best practice performance as well.