SQL Server WRITELOG waits: diagnosing transaction log throughput, commit patterns and when delayed durability is acceptable

#Symptoms

• WRITELOG is at or near the top of sys.dm_os_wait_stats, and average wait per task is above a couple of milliseconds.
• Insert-heavy batch jobs or OLTP commits slow down while CPU is low.
• Throughput stops scaling when you add application threads.
• On an AG primary, HADR_SYNC_COMMIT sits right next to WRITELOG. On Azure SQL, LOG_RATE_GOVERNOR or POOL_LOG_RATE_GOVERNOR shows up.

WRITELOG means a session committed (or a log block filled) and is waiting for the log flush to reach disk. There are only a few root causes, and each needs a different fix.

#How a log flush works

Log records go into an in-memory log buffer. A flush writes the buffer to the log file when a transaction commits, a buffer fills (log blocks are up to 60 KB), or a checkpoint needs it. The committing session waits on WRITELOG until the write finishes. Each database has one log file being written sequentially, and the number of outstanding log I/Os per database is limited, so log latency directly caps commit rate.

Two shapes of problem follow:

1. Each flush is slow: storage latency on the log volume is high.
2. There are too many flushes: thousands of single-row autocommit transactions each force a tiny flush.

#Step 1: get the wait numbers

SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       CAST(wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0) AS decimal(10,2)) AS avg_ms
FROM   sys.dm_os_wait_stats
WHERE  wait_type IN ('WRITELOG', 'HADR_SYNC_COMMIT', 'LOG_RATE_GOVERNOR',
                     'POOL_LOG_RATE_GOVERNOR', 'LOGBUFFER')
ORDER  BY wait_time_ms DESC;

These counters are cumulative since startup. Take two snapshots a few minutes apart during the slow period and compare the deltas; a since-boot average hides the incident.

LOGBUFFER means sessions are waiting for space in the log buffer itself, which usually follows from slow flushes.

#Step 2: measure log file latency

SELECT DB_NAME(vfs.database_id) AS database_name,
       mf.physical_name,
       vfs.num_of_writes,
       vfs.num_of_bytes_written / 1048576 AS mb_written,
       CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0) AS decimal(10,2)) AS avg_write_ms,
       CAST(vfs.num_of_bytes_written * 1.0 / NULLIF(vfs.num_of_writes, 0) / 1024 AS decimal(10,2)) AS avg_write_kb
FROM   sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN   sys.master_files AS mf
       ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
WHERE  mf.type_desc = 'LOG'
ORDER  BY vfs.io_stall_write_ms DESC;

Read the two derived columns together:

Pair it with the per-database Performance Monitor counters, which are easy to sample from T-SQL:

SELECT instance_name AS database_name, counter_name, cntr_value
FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE '%:Databases%'
  AND  counter_name IN ('Log Flushes/sec', 'Log Bytes Flushed/sec',
                        'Log Flush Wait Time', 'Log Flush Waits/sec')
  AND  instance_name NOT IN ('_Total', 'mssqlsystemresource')
ORDER  BY database_name, counter_name;

The /sec counters are cumulative raw values in this DMV. Sample twice and divide the difference by the elapsed seconds.

#Step 3: identify the cause

#Slow log storage

Check the volume holding the .ldf:

• Log shares a volume with data files, tempdb or backups.
• On Azure VMs, the log disk has read caching enabled (log disks should use None), or the VM's uncached throughput or IOPS cap is reached before the disk's cap.
• On EC2, the EBS volume or the instance's EBS bandwidth is saturated; check CloudWatch VolumeQueueLength and VolumeWriteBytes.
• SAN write cache disabled or a storage replication layer adding synchronous latency.

Get-Counter '\LogicalDisk(L:)\Avg. Disk sec/Write','\LogicalDisk(L:)\Disk Write Bytes/sec' -SampleInterval 5 -MaxSamples 12

Fix by moving the log to dedicated low-latency storage (Premium SSD v2 or Ultra Disk on Azure, io2 or gp3 with provisioned throughput on AWS), and checking the VM or instance limits as well as the disk limits.

#Too many small commits

Find the statements committing most often:

SELECT TOP (20)
       qs.execution_count,
       qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS avg_elapsed_us,
       SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, 200) AS statement_text
FROM   sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE  st.text LIKE '%INSERT%' OR st.text LIKE '%UPDATE%'
ORDER  BY qs.execution_count DESC;

The classic pattern is a loader doing row-by-row INSERT with autocommit. Batching 500-5,000 rows per transaction turns thousands of flushes into a few:

DECLARE @batch int = 2000;
WHILE 1 = 1
BEGIN
    BEGIN TRAN;
    INSERT INTO dbo.Target (Id, Payload)
    SELECT TOP (@batch) s.Id, s.Payload
    FROM   dbo.Staging AS s
    WHERE  NOT EXISTS (SELECT 1 FROM dbo.Target AS t WHERE t.Id = s.Id)
    ORDER  BY s.Id;
    IF @@ROWCOUNT = 0 BEGIN COMMIT; BREAK; END;
    COMMIT;
END;

Keep batches small enough that lock escalation (around 5,000 locks on one object) and log growth stay under control. Table-valued parameters, SqlBulkCopy and BULK INSERT reduce flushes further.

#Availability Group synchronous commit

On a primary with a synchronous secondary, commit waits for the local flush (WRITELOG) and the remote harden (HADR_SYNC_COMMIT). Slow secondary log storage or network latency both land here.

SELECT ar.replica_server_name, drs.database_id,
       drs.log_send_queue_size, drs.log_send_rate,
       drs.redo_queue_size, drs.last_commit_time
FROM   sys.dm_hadr_database_replica_states AS drs
JOIN   sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id;

Secondary log storage must be as fast as the primary's. Cross-region replicas should be asynchronous.

#Log rate governance on Azure SQL

Azure SQL Database and Managed Instance cap log generation per service objective. LOG_RATE_GOVERNOR means you hit that cap; faster storage will not help.

SELECT TOP (30) end_time, avg_log_write_percent
FROM   sys.dm_db_resource_stats
ORDER  BY end_time DESC;

Sustained values near 100% need a larger service objective, Hyperscale, or less logging (batching, minimally logged loads into heaps with TABLOCK, avoiding unnecessary index maintenance). Check the current per-tier log rate limits in Microsoft's resource limits documentation.

#When delayed durability is acceptable

Delayed durability lets commit return before the log flush. Flushes then happen when the buffer fills, on a durable commit, or when you call sys.sp_flush_log.

ALTER DATABASE StagingDB SET DELAYED_DURABILITY = ALLOWED;

-- In the loader only
BEGIN TRAN;
    -- ... work ...
COMMIT TRAN WITH (DELAYED_DURABILITY = ON);

-- At the end of each load step
EXEC sys.sp_flush_log;

FORCED applies it to every transaction in the database; ALLOWED lets individual commits opt in.

Caution

With delayed durability, a crash, failover or unexpected shutdown loses the transactions that were committed but not yet flushed, up to about one log buffer's worth. The application has already been told they succeeded. Use it only where the data can be regenerated (staging, ETL scratch, telemetry you can replay), never for orders, payments or anything an application acknowledges to a user.

On an AG, delayed-durable transactions that were not flushed are not sent to the secondary either, so failover loses them too.

#Prevent it next time

• Put transaction logs on dedicated volumes, sized for throughput and latency rather than capacity; disable host read caching for log disks on Azure VMs.
• Baseline avg_write_ms for log files and alert when it doubles.
• Add an application standard: no row-by-row autocommit loaders; batch or bulk load.
• Track WRITELOG and HADR_SYNC_COMMIT average wait per task as deltas, not since-boot totals.
• On Azure SQL, alert on avg_log_write_percent above 80% for 15 minutes.
• Keep VLF counts reasonable and pre-size the log so autogrowth does not stall writes during peaks.