WRITELOG

This wait occurs when data in the log cache is being flushed to disk. Whenever a data page is updated, it’s written to the buffer cache and the log cache. Data in the log cache is then written to the transaction log file on the physical disk once the transaction is complete. If log flushes are consistently waiting, you need to investigate further.

If this wait occurs in isolation, the most common causes are operations such as checkpoints and frequent transaction commits, or disk subsystem performance issues. If it exists in combination with at least one of the following waits, your servers could be experiencing I/O problems:

  • ASYNCH_IO_COMPLETION
  • IO_COMPLETION
  • LOGMGR
  • PAGEIOLATCH_*

Investigating general I/O issues

If there’s evidence from other waits that an I/O problem exists, find out which operation is causing it, and where the bottleneck exists: 

  • Check the affected queries and the Top queries table. Order by logical writes and physical reads to find the most I/O intensive, and check for disk-related wait types. Tune them if necessary; check for missing indexes, or indexes containing an incorrect sort order. See Query tuning and Showplan Operator of the Week - SORT.
  • Check the following metrics on the Analysis page to see which systems are affected. See each metric’s Description tab for guideline values and possible solutions. They may indicate bottlenecking or excessive pressure on the database engine:
    • Disk avg. read time
    • Disk avg. write time
    • Avg. disk queue length
    • Buffer cache hit ratio
    • SQL Server: free memory
    • Buffer page life expectancy
    • Machine: memory used
  • As a last resort, review whether disk resources are adequate for your server load:
    • If your disk drivers and BIOS are out of date, consider upgrading.
    • Consider splitting data across different physical drives to distribute the I/O workload more evenly.

Investigating disk performance issues

  • If the disk metrics listed above are high, but values for the other metrics are normal, it suggests the transaction log file is under pressure.
  • Consider moving the transaction log to its own disk to avoid contention with other files.
  • As a last resort, consider increasing disk capacity.

Investigating operational issues

  • Check the following metrics on the Analysis page to see if frequent commits are causing the issue:
    • Log bytes flushed/sec
    • Log flushes/sec
    • Log flush waits/sec
  • Use PerfMon to look for high Buffer Manager\Checkpoint pages/sec values. Checkpoints are markers in the transaction log used for data recovery. They make sure dirty pages related to commits are flushed to disk, but if there are too many pages, checkpoints take longer to complete.
  • Check for queries with a lot of WRITELOG waits, and consider reducing the number of transaction commits or checkpoints for these batches.
  • If you’re using a looping mechanism, check whether commits are taking place inside or outside the loop. Multiple small commits inside the loop will have a greater impact on WRITELOG.

WRITELOG and synchronous replication

If you're using availability groups, synchronous replication can affect the WRITELOG wait, sometimes causing it to be avoided entirely because it is effectively obscured by the HADR_SYNC_COMMIT wait.

With synchronous replication, the primary replica sends log records to the secondary replica and then waits for the secondary to receive and harden these records and send back acknowledgement to the primary, before committing locally. The HADR_SYNC_COMMIT wait represents the time that the primary replica waits for this acknowledgement from the secondary, whereas the WRITELOG wait represents the time it takes to write the log locally.

These two waits begin at the same time. However, the HADR_SYNC_COMMIT wait tends to be longer than the WRITELOG wait, meaning that the WRITELOG wait often doesn’t appear as a separate delay.


Didn't find what you were looking for?