Trace Flag 8904 - Disable Inline Database Log Flushes

A log cache block holds log records for the database. Many workers are allowed to add log records to the same log cache buffer (LC) in parallel.  When the log cache block becomes full, or a commit request requires the block to be saved to stable media the log cache buffer is 'flushed.'

SQL Server optimizes database log file flush requests, performing these flush requests inline on the active worker. Certain patterns of log record activity may encounter increased spinlock contention while performing the log cache block flush activities.

Trace flag -T8904 (startup only trace flag) disables inline log flush, limiting the contention possibility from many workers to the subset of background LogWriter workers.  When the trace flag is enabled, the worker adding log records mark the log cache block to be flushed and a background LogWriter worker performs the flush activity.

Reference: KB5004649 - FIX: Parallel redo failure on secondary replica in SQL Server 2019 - Microsoft Support

Troubleshooting

Capturing the XEvent spinlock backoff events (histogram bucketing is helpful) and symbolizing the call stacks may help confirm your workload is impacted by the log cache spinlock contention from inline flush activities.

I worked with Lonny Niederstadt, who used the following XEvent session to confirm the inline log cache flush behavior the system.

CREATE EVENT SESSION [LogflushQ_spinlock_backoff] ON SERVER
ADD EVENT sqlos.spinlock_backoff(
    ACTION(    package0.callstack,package0.collect_cpu_cycle_time,sqlos.cpu_id,sqlos.numa_node_id
    ,        sqlos.scheduler_id,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_resource_group_id
    ,        sqlserver.session_resource_pool_id)
    WHERE ([type]=(129))),
ADD EVENT sqlos.spinlock_backoff_warning
ADD TARGET package0.event_file(SET filename=N'LogflushQ_spinlock_backoff',max_file_size=(100),max_rollover_files=(8))
WITH (    MAX_MEMORY=80 MB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS
,        MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

The following is an example stack of the inline flush spinlock usage, symbolized stack.

sqlmin!Spinlock<129,9,258>::SpinToAcquireOptimistic
sqlmin!SQLServerLogMgr::FlushLCOld
      ... Inline FlushLC ...
sqlmin!SQLServerLogMgr::AppendLogRequest
sqlmin!SQLServerLogMgr::ReserveAndAppend
...

Here is the pseudo-code decision used in AppendLogRequest to make the inline or mark, flush decision.

if (useDelayedDurability || false == shouldInlineLogIo)
{
    MarkLCForFlush(oldLC, TRUE, useDelayedDurability);
}
else
{
    FlushLC (TRUE);
}

Additional Information

The log cache buffer spinlock contention is more pronounced when async I/O encounters latencies.  The inline I/O typically scales better on larger machines with fast async I/O versus the additional latency added to mark and wait for a LogWriter to process the flush request.

SQL Server 2022 also has a safeguard detecting async I/O delays and may automatically disable the inline I/O when delays are encountered.