Your wait list shows WRITELOG waits at the top, and the storage team says the disks are healthy. Both facts can be true. That wait measures time spent waiting for log records to become durable, so the next step is to separate slow writes from too many small commits.
What WRITELOG Waits Actually Measure
SQL Server writes log records before it can confirm a fully durable commit. WRITELOG time can rise when log writes take too long, when the application commits very frequently, or when both happen together. A total wait count since startup is not a diagnosis. Look at change over a representative interval, and compare it with current workload and log file behavior.
I start by asking whether response time is poor for a specific transaction or throughput is capped across the system. Those are different symptoms. A log file can show reasonable average latency while an application commits one row at a time. Conversely, a batch of sensible commits can still wait on a slow log destination. What does one transaction look like in the workload that users notice?
Snapshot WRITELOG Waits Twice
sys.dm_os_wait_stats reports cumulative WRITELOG counts and time. Record a snapshot, wait through the problem interval, and take a second snapshot. Subtract the first from the second. Do not reset server-wide wait stats just to make your arithmetic easy; that erases evidence for everyone. The query below gives the current cumulative figures, not an interval rate by itself.
SELECT wait_type, waiting_tasks_count,
wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = N'WRITELOG';A high count with small waits suggests a different pattern from fewer long stalls. Signal wait time is time waiting for CPU after the resource is ready, so do not add it again to wait_time_ms as if it were a separate delay. Pair this snapshot with log file I/O counters and application commit counts. One number seldom explains a log bottleneck.
Measure Each Log File's Writes
sys.dm_io_virtual_file_stats reports cumulative writes and I/O stall time by file. Join to sys.master_files and filter type_desc = LOG. Calculate average write stall only as a first clue. Averages can hide spikes, and counters include earlier work since their reset point. Two snapshots during the slowdown are stronger than a lifetime average.
SELECT DB_NAME(v.database_id) AS database_name,
mf.name AS logical_file_name,
v.num_of_writes, v.num_of_bytes_written,
v.io_stall_write_ms,
CONVERT(decimal(12,2),
1.0 * v.io_stall_write_ms / NULLIF(v.num_of_writes, 0))
AS average_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS v
JOIN sys.master_files AS mf
ON mf.database_id = v.database_id AND mf.file_id = v.file_id
WHERE mf.type_desc = N'LOG'
ORDER BY average_write_ms DESC;Check the database that owns the slow transactions. A server-wide WRITELOG wait total does not identify it. Also inspect write size by dividing bytes written by write count. A flood of small writes can point to commit frequency. If the log file has long stalls during the same interval, examine the storage path, competing I/O, and log growth events.
Batch Commits to Cut WRITELOG Waits
Committing every row separately can force far more durable flush points than committing a modest batch. The application should group logically related rows in transactions with a bounded size. Do not wrap an entire night of work in one giant transaction. That holds locks longer, grows the log, delays truncation, and makes rollback unpleasant. A few hundred or thousand rows per batch can be a test, not a universal rule.
I compare rows per transaction and log write counts before and after a change. The application team should preserve error handling and idempotency. If a batch fails halfway, the retry must not duplicate business work. The fastest write benchmark is useless when recovery semantics change without anyone noticing. A transaction boundary is part of the application's promise.
Move the Log Only When Storage Is the Constraint
If interval measurements show long log write latency, inspect the underlying volume, controller, virtualization layer, and competing jobs. Moving the log to a lower-latency path can help. First verify free space, throughput, backup procedures, and failover behavior. A move to a faster-looking drive that shares the same busy backend changes the drive letter, not the bottleneck.
Multiple log files in one database do not stripe writes for general throughput. SQL Server uses the log sequentially. Add a second log file for an exceptional recovery or capacity reason, not as a default WRITELOG fix. Review autogrowth settings too. Repeated tiny growth events create pauses and operational noise. Pre-size to a measured workload and keep enough headroom for maintenance.
Treat Delayed Durability as a Business Decision
Delayed durability lets a commit return before its log record is guaranteed on disk. It can reduce commit waits, but a crash can lose transactions that the client was told had committed. That is a data-loss policy, not a harmless speed switch. SQL Server supports ALLOWED for selected transactions and FORCED at database scope. Start with ALLOWED only if the application owner accepts the exposure and can identify eligible transactions.
SELECT name, delayed_durability_desc
FROM sys.databases
WHERE database_id = DB_ID();This query only reads the current setting. Do not turn it on to make a wait graph prettier. Confirm restore, availability, and application expectations before any change. For financial or order data, fully durable commits are generally the right contract. The log writer should not become faster by quietly changing what committed means.
Remove Log Volume That Has No Value
Audit verbose row-by-row updates, repeated writes of unchanged values, oversized indexes maintained during bulk loads, and redundant staging work. These can generate log records without improving the result. Change one source of volume at a time and measure bytes written, transaction rate, WRITELOG deltas, and user latency. Do not confuse a smaller log file with less log generation; shrinking changes file size, not the application's write pattern.
Keep a short timeline of the slowdown and the evidence. If latency is low but commit frequency is extreme, work with the application on batching. If latency spikes with ordinary commit volume, investigate storage. If both are high, fix the safe workload pattern and the I/O path in a measured order. A wait name is the start of the investigation, not the verdict.
Related reading on this blog: Delayed Durability and Flushing Log Files and Transaction Logs: The Good, The Bad, and The Ugly.
WRITELOG is not a fix instruction, it is a clue to the real logging delay.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
