WRITELOG

(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

(Back to main page…)

Description:

This wait type is when a thread is waiting for a log block to be written to disk by an asynchronous I/O. A log block is written to disk when:

  • A transaction commits (unless it is set to be delayed durable in SQL Server 2014 and above)
  • The log block reaches its maximum size of 60Kb
  • A data file page is being written to disk and write-ahead logging forces the current log block to disk (as the log block contains the most recent log record describing a change to the data file page being written out)
  • The sp_flush_log proc is executed (in SQL Server 2014 and above, for log management with delayed durability)

It’s extremely common to see WRITELOG as one of the top waits on servers, but there are still plenty of things you can do to try to alleviate them – see below.

(Books Online description: “Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.”)

Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:

Pre-2005/2005

Removed in SQL Server version:

N/A

Extended Events wait_type value:

The map_key value in sys.dm_xe_map_values is 178 in 2008 and 2008 R2, and 182 in 2012 and 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.

Other information:

There are many things you can do to reduce WRITELOG waits and wait times, including:

  • Reduce the amount of transaction log being generated
  • Reduce how often log flushes are occurring
  • Reduce/remove log flushing overheard from synchronous HA technologies
  • Upgrade to 2012 or higher to raise the max-outstanding-log-writes from 32 to 112
  • Put the transaction log on the fastest portion of the I/O subsystem
  • Consider implementing delayed durability (in 2014+) or in-memory OLTP/Hekaton

I’ve written several articles on sqlperformance.com that you should read to learn more about this these and troubleshooting transaction log performance:

Known occurrences in SQL Server (list number matches call stack list):

  1. Committing a regular transaction
  2. Forcing a log flush because of a page being written to disk during a checkpoint
  3. Committing a transaction while performing a deferred drop of an allocation unit (see this article for an explanation)

And many, many more similar stacks to do with writing log blocks to disk.

Abbreviated call stacks (list number matches known occurrences list):

  1. SOS_Task::PostWait+90
    SQLServerLogMgr::WaitLCFlush+1d5
    SQLServerLogMgr::LogFlush+1ee
    SQLServerLogMgr::WaitLogWritten+1a
    RecoveryUnit::HardenLog+37a
    XdesRMFull::CommitInternal+5c1
    XactRM::SinglePhaseCommit+20d
    XactRM::CommitInternal+4b9
    FullXactImp::Commit+326
    CMsqlXactInternalReadWrite::Commit+15
    CMsqlXactImp::Commit+1d2
    CXStmtDML::FinishNormalImp+3c
    CMsqlExecContext::ExecuteStmts<1,1>+4ea
    CMsqlExecContext::FExecute+a33
    CSQLSource::Execute+866
  2. SOS_Task::PostWait+90
    SQLServerLogMgr::WaitLCFlush+1d5
    SQLServerLogMgr::LogFlush+1ee
    SQLServerLogMgr::WaitLogFlush+18
    BPool::PageReadyToWrite+3df
    BPool::WriteMultiple+215
    BPool::FlushCache+608
    HardenAndLogCheckpoint+639
    CheckpointRU2+26c
    AsynchronousDiskAction::DoFlushCache+88
    AsynchronousDiskAction::ExecuteDeferredAction+242
    AsynchronousDiskWorker::ThreadRoutine+15c
    SubprocEntrypoint+a59
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+a8
  3. SOS_Task::PostWait+90
    SQLServerLogMgr::WaitLCFlush+1d5
    SQLServerLogMgr::LogFlush+1ee
    SQLServerLogMgr::WaitLogWritten+1a
    RecoveryUnit::HardenLog+37a
    XdesRMFull::CommitInternal+5c1
    XactRM::SinglePhaseCommit+20d
    XactRM::CommitInternal+4b9
    FullXactImp::Commit+326
    SqlAutoSimpleXact::CommitInternal+3a
    DeferredAllocUnitDrop::Process+1fa
    DeferredAllocUnitDrop::AllocUnitCleanupTask+76
    PeriodicTimerTask::ProcessTskPkt+a
    TaskReqPktTimer::ExecuteTask+63
    OnDemandTaskContext::ProcessTskPkt+285
    SystemTaskEntryPoint+431
    OnDemandTaskContext::FuncEntryPoint+25
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+a8