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:


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 10 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
    • Buffer free pages
    • 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.


Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com

Didn't find what you were looking for?