SQL Server WRITELOG waits: commit latency from a slow transaction log

WRITELOG is the wait type SQL Server records when a worker thread blocks on a transaction log flush. Write-ahead logging requires the log block to be hardened to disk before the engine acknowledges a commit, so WRITELOG directly bounds write throughput. When it dominates your top-waits list, every write transaction is paying a latency tax at commit.

The reported symptom is rarely “WRITELOG is high.” It is commit latency, write transaction timeouts, application retry storms, Availability Group replication lag, or batch requests/sec collapsing while CPU sits low. WRITELOG is the in-engine signature you find in sys.dm_os_wait_stats or sys.dm_exec_session_wait_stats. It tells you the bottleneck is in the log write path: the storage below the log file, the I/O stack between SQL Server and that storage, or the commit rate the Log Writer is being asked to service. Tuning queries will not fix it.

What this means

WRITELOG is recorded on the worker thread that issued the commit (or any operation that forces a log flush). A log flush is triggered when:

• A transaction commits and must be hardened before the engine acknowledges it.
• A log block fills to its maximum size of 60KB.
• Write-ahead logging forces a flush before a dirty data page can be written.
• sp_flush_log is executed (used by natively compiled procedures).

The flush is issued by the Log Writer. Pre-2016 there was a single Log Writer thread. SQL Server 2016 added up to 4 Log Writer threads. SQL Server 2019 raised that to 8 and also allows regular worker threads to issue log writes directly. When the storage below the log file is slow, every commit pays that latency.

The coupling between commit and flush is what makes WRITELOG damaging in OLTP. In a high-rate insert/update workload with thousands of small transactions per second, even 5ms of log write latency becomes a serialization point. There is no batching across transactions unless the application batches. The log is a serial stream within each database.

flowchart TD
    A[App commits txn] --> B[Engine builds log block]
    B --> C[Log Writer flushes block to disk]
    C --> D{I/O completes?}
    D -- yes --> E[Commit ack sent to client]
    D -- slow / no --> F[Worker records WRITELOG]
    F --> C
    C -.->|single serial path| G[Every other commit in this DB]

Every concurrent write transaction in the same database funnels through this single path.

Common causes

Quick checks

All read-only. Run them in order.

-- 1. Top waits since last reset. For a live problem, snapshot twice 60s apart and compute deltas.
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
    'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP',
    'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'XE_TIMER_EVENT',
    'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'BROKER_EVENTHANDLER', 'SP_SERVER_DIAGNOSTICS_SLEEP',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'DIRTY_PAGE_POLL',
    'DISPATCHER_QUEUE_SEMAPHORE', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
    'QDS_ASYNC_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'HADR_WORK_QUEUE',
    'BROKER_TRANSMITTER', 'KSOURCE_WAKEUP'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- 2. Per-file write latency. JOIN sys.master_files; do NOT assume file_id = 2.
SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.name AS file_name,
    mf.type_desc,
    vfs.num_of_writes,
    CASE WHEN vfs.num_of_writes > 0
         THEN vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes
         ELSE 0 END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
WHERE mf.type_desc = 'LOG'
ORDER BY avg_write_latency_ms DESC;

-- 3. Outstanding I/O on the log file (helps detect the 112-cap scenario on fast SSD)
SELECT
    DB_NAME(database_id) AS database_name,
    file_id,
    io_pending,
    io_pending_ms_ticks
FROM sys.dm_io_pending_io_requests
WHERE io_pending = 1
ORDER BY database_id, file_id;

-- 4. Log flush wait time per database (cumulative; compute deltas)
SELECT
    instance_name AS database_name,
    cntr_value AS log_flush_wait_time_ms
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log Flush Wait Time'
  AND object_name LIKE '%Databases%'
  AND instance_name NOT IN ('_Total', 'mssqlsystemresource');

-- 5. Log flush rate vs log bytes flushed per database
SELECT
    instance_name AS database_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Log Flushes/sec', 'Log Bytes Flushed/sec', 'Transactions/sec')
  AND object_name LIKE '%Databases%'
  AND instance_name NOT IN ('_Total', 'mssqlsystemresource');

-- 6. Confirm synchronous AG contribution
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('HADR_SYNC_COMMIT', 'WRITELOG');

Query 7 below is a Windows command, not SQL. Run it from an elevated command prompt:

fltmc instances

Queries 1 and 2 are decisive. If query 2 shows the LOG file averaging more than 5ms per write, storage is the cause. If query 2 shows sub-millisecond writes but WRITELOG is still dominant, suspect the outstanding-I/O cap on fast SSDs, filter drivers, or AG synchronous commit.

How to diagnose it

1. Snapshot sys.dm_os_wait_stats twice, 60 seconds apart, and compute the delta. Cumulative-since-startup values are useless for a live problem. WRITELOG must be a meaningful fraction of the delta.

2. Run query 2 as a delta as well. Thresholds for log write latency: under 2ms healthy, 2-5ms acceptable, above 5ms degraded, above 15ms severe.

3. If the LOG file shows sub-millisecond writes but WRITELOG is still dominant, check sys.dm_io_pending_io_requests for sustained near-112 outstanding requests per database. That is the per-database outstanding-I/O cap, raised from 32 in SQL Server 2008 to 112 starting in SQL Server 2012. On very fast SSDs at very high commit rates, this cap becomes the bottleneck rather than disk latency.

4. Run fltmc instances from an elevated command prompt. If PerfMon shows healthy disk latency but SQL Server reports WRITELOG, the bottleneck is in the I/O stack between SQL Server and the Partition Manager: antivirus, backup agents, encryption products. Microsoft’s official guidance is to exclude SQL Server data, log, and backup files from real-time scanning.

5. If you are running synchronous-commit Availability Groups, look at HADR_SYNC_COMMIT. In synchronous mode the primary waits for the secondary to harden the log before acknowledging the commit. HADR_SYNC_COMMIT typically dwarfs WRITELOG in that case, but WRITELOG still contributes. Check the secondary’s log-file I/O stall and the AG send queue.

6. Distinguish storage latency from commit-rate pressure. Compare log bytes flushed per second against transactions per second. If transactions per second is very high and log bytes per transaction is small (single-row inserts or updates), the engine is asking the Log Writer to flush a small block for every commit. The fix is in the application, not the storage.

7. Check VLF count via sys.dm_db_log_info(DB_ID('<db>')) (SQL 2016 SP2+) or DBCC LOGINFO. Excessive VLFs (more than a few hundred) slow log operations including writes, backup, and recovery. This is a secondary cause but worth ruling out.

Metrics and signals to monitor

Fixes

Slow log-file storage

The most common cause and the most direct to fix. Move the log file to faster, dedicated storage. Log writes are sequential, so a single well-placed log file on low-latency disk (NVMe preferred, then SSD) outperforms multiple log files on slower shared storage.

If you cannot move the file immediately, isolate the log volume from data, TempDB, and backups. Log writes are latency-sensitive; any competing I/O on the same spindle hurts.

Do not add a second log file to parallelize log writes. SQL Server does not parallelize across log files within a database. Multiple log files are only useful for space, never for performance.

Filter driver overhead

Run fltmc instances from an elevated command prompt. Antivirus, backup agents, and encryption products insert themselves between SQL Server and the disk. Exclude SQL Server data, log, and backup files from real-time AV scanning. If you cannot exclude the files, schedule scans for maintenance windows.

Fast-SSD outstanding-I/O cap

If writes are sub-millisecond and the system is hitting the 112 outstanding-I/O cap per database, the bottleneck is not storage latency but commit serialization. Options:

• Batch commits in the application. Group multiple writes into a single transaction so each log flush carries more bytes.
• Use delayed durability carefully (see below).
• Upgrade to SQL Server 2019 or later, which adds up to 8 Log Writer threads and allows regular worker threads to issue log writes directly.

Tiny transactions committing individually

The fix is in the application. Each BEGIN TRAN … COMMIT for a single row insert forces a separate log flush. Re-architecting to batch (one transaction per N rows, or one transaction per business operation rather than per row) reduces flush count dramatically without changing storage.

Measure before changing code. Compare Log Flushes/sec to Transactions/sec. If they are nearly equal, every transaction is forcing its own flush. If Log Flushes/sec is much lower, transactions are already being grouped by the engine when their log records combine within the 60KB block window.

Synchronous-commit AlwaysOn

In synchronous-commit AGs, HADR_SYNC_COMMIT will be the larger wait, but WRITELOG still contributes. Investigate the secondary’s log-file I/O stall. If the secondary storage is slower than the primary, that latency is reflected back to the primary through the synchronous-commit handshake.

If commit latency is critical and the secondary cannot keep up, options include upgrading secondary storage, investigating network round-trip between replicas, or temporarily switching to asynchronous commit during the incident. Switching to asynchronous commit creates a data-loss window; acknowledge the risk explicitly before making the change.

Delayed durability as a last resort

SQL Server 2014+ supports delayed durability at the database level (DISABLED / ALLOWED / FORCED), the commit level, or the atomic block level. With delayed durability, the engine acknowledges the commit before the log flush completes, removing WRITELOG from the critical path.

This is a tradeoff, not a fix. You gain commit latency and lose ACID durability. On crash, transactions acknowledged as committed may be rolled back during recovery. Delayed durability is incompatible with transactional replication, Change Data Capture, cross-database and DTC transactions, and Azure Synapse Link. Starting with SQL Server 2022 CU2 and SQL Server 2019 CU20, attempting to enable it alongside these features raises errors 22891 or 22892.

It also hides the underlying problem. WRITELOG disappears from the waits list because the engine is no longer waiting, but the storage is still slow. Use it deliberately for known workloads where the data-loss tradeoff is acceptable, not as a general performance tweak.

Prevention

• Pre-size log files. Log auto-growth is expensive. Instant File Initialization does not apply to log files, so each growth event requires zero-initialization and pauses all log writes to that file. Pre-allocate based on the volume of log generated between log backups.
• Keep VLF count low. Periodically check via sys.dm_db_log_info or DBCC LOGINFO. If the count is in the thousands, shrink and regrow the log in appropriate increments.
• Keep the log on dedicated, low-latency storage. NVMe or SSD is the baseline for OLTP.
• Validate filter driver exclusions as part of deployment. AV exclusions drift over time as security tooling changes.
• Baseline WRITELOG and log write latency. The most common failure mode is not knowing whether current WRITELOG is normal or new. Snapshot wait stats periodically (30 to 60 seconds) and store the deltas externally. DMVs reset on instance restart.
• For applications committing in tight loops, plan batching as a capacity practice, not a one-off fix.

How Netdata helps

• Per-second collection of SQL Server wait statistics makes WRITELOG visible as a time series rather than a single cumulative number, so you can correlate a WRITELOG spike with a deploy, a traffic spike, or a storage event.
• Correlating WRITELOG with Log Flush Wait Time and per-file write latency from sys.dm_io_virtual_file_stats separates storage-driven WRITELOG from commit-rate-driven WRITELOG in a single view.
• Per-second Transactions/sec, Batch Requests/sec, and Log Flushes/sec surface the tiny-transactions pattern: high commit rate, low bytes per commit.
• For AlwaysOn deployments, pairing WRITELOG with HADR_SYNC_COMMIT waits and send-queue size tells you whether the bottleneck is local storage or replica hardening.
• ML-based anomaly detection on log write latency and outstanding I/O catches gradual storage degradation before WRITELOG becomes the dominant wait.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• How Microsoft SQL Server actually works in production: a mental model for operators
• SQL Server log backups missing: the full-recovery log that grows forever
• SQL Server log_reuse_wait_desc: why the transaction log will not truncate
• SQL Server transaction log percent used climbing toward full
• Microsoft SQL Server monitoring checklist: the signals every production instance needs
• Microsoft SQL Server monitoring maturity model: from survival to expert
• SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss