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
  • With availability groups, the remote log copy incurs a HADR_SYNC_COMMIT wait
• 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:

• Transaction Log Monitoring
• Trimming the Transaction Log Fat
• Trimming More Transaction Log Fat
• Transaction Log Configuration Issues

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