Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Knowing what SQL Server does with an INSERT, UPDATE or DELETE explains most of the advice about log files, checkpoints, recovery time and why COMMIT can be slow.

The steps for a single UPDATE

\1. The query processor finds the rows to change, using an index if there is one.

\2. The pages holding those rows are read from disk into the buffer pool (memory) if they are not there already.

\3. Locks are taken: intent locks on the table and page, and exclusive (X) locks on the rows.

\4. A log record describing the change is written to the log buffer in memory.

\5. The page is changed in memory and marked dirty. Nothing is written to the data file yet.

\6. On COMMIT, the log buffer is flushed to the transaction log file on disk. Only when that write is confirmed does COMMIT return to the client.

\7. The locks are released.

\8. Later, a checkpoint (or the lazy writer, when memory is short) writes the dirty data pages to the data file.

Write-ahead logging

The rule is that a log record always reaches disk before the data page it describes. That is why a committed change is safe even though the data file has not been updated: after a crash, recovery reads the log, redoes committed work that never reached the data file, and undoes uncommitted work that did.

This is also why the transaction log’s write speed sets the speed of every commit. A busy system waiting on WRITELOG is waiting for the log disk.

-- How long commits wait for the log disk
SELECT wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN (N'WRITELOG', N'LOGBUFFER');

-- Write latency on each log file
SELECT DB_NAME(vfs.database_id) AS database_name, mf.physical_name,
       vfs.num_of_writes,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
WHERE mf.type_desc = N'LOG'
ORDER BY avg_write_ms DESC;

As a rough guide, log writes should average under 2 ms on good storage. Over 5 ms and users will notice on busy systems.

Seeing the dirty pages

-- Dirty pages in the buffer pool for each database
SELECT DB_NAME(database_id) AS database_name,
       COUNT(*) AS dirty_pages,
       COUNT(*) * 8 / 1024 AS dirty_mb
FROM sys.dm_os_buffer_descriptors
WHERE is_modified = 1
GROUP BY database_id
ORDER BY dirty_pages DESC;

Run CHECKPOINT in a test database and run it again: the dirty page count for that database drops to (nearly) zero.

Checkpoints and recovery time

Checkpoints limit how much log has to be replayed after a crash. Databases created on SQL Server 2016 or later use indirect checkpoints with a target recovery time of 60 seconds; older databases upgraded in place may still show 0 (automatic checkpoints), which causes bigger bursts of writes.

SELECT name, target_recovery_time_in_seconds
FROM sys.databases
ORDER BY name;

-- Move an older database to indirect checkpoints
ALTER DATABASE [YourDB] SET TARGET_RECOVERY_TIME = 60 SECONDS;

Delayed durability

Delayed durability lets COMMIT return before the log is flushed. It can speed up systems that commit thousands of tiny transactions, but a crash or failover can lose the last few committed transactions. Only use it where losing them is acceptable, and never on a database that holds anything financial or legal.

SELECT name, delayed_durability_desc FROM sys.databases;
-- ALTER DATABASE [YourDB] SET DELAYED_DURABILITY = DISABLED;  -- the default

What this means in practice

• Put transaction logs on the fastest, lowest-latency storage, separate from data files where possible.
• Many tiny transactions each wait for a log flush. Batching rows into transactions of a few thousand is far faster than committing one row at a time.
• A long-running transaction keeps the log from being truncated, however often you back it up.
• A large ROLLBACK takes about as long as the work it undoes, and the same is true of crash recovery for an open transaction.

From my SQL Server DBA handbook.