Blog Post: What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server

What does WRITELOG waittype mean?

WRITELOG waits represent the time waiting on a LOG I/O to complete. Common operations that cause log flushes are checkpoints and transaction commits.

Transaction commits can be whenever you run a COMMIT TRAN (incase of Explicit transaction) or whenever SQL Server commits the transaction on behalf of you (Implicit transaction)

Before we continue further, please read an Excellent article from SQL Customer Advisory Team at http://sqlcat.com/technicalnotes/archive/2008/12/09/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager.aspx which talks about a throttle limit called "Outstanding I/O limit" and "Outstanding log I/O Limit".  So this is another condition where you will notice WRITELOG wait type when outstanding I/O limit is reached.

Given below are the conditions (but not limited to) when a log block will be flushed to disk:

1. COMMIT XSN

2. CHECKPOINT

3. LOG Block is full

More information about #3 LOG BLOCK:

You might be knowing that LOG entries are first written serially to in-memory structure called Log block and once the buffer is full or any of the transaction COMMITS, then the entire log block is flushed to disk. It is important to note that multiple active transactions can be interleaved in the log buffer, and if any one transaction commits, the buffer memory is written to the transaction log file by the log writer. No need to worry about what happens to data integrity when multiple transactions interleave at same time because SQL Server implements synchronization objects like spinlocks, mutex to control the access to log buffer.

The size of this log block can be between 512 bytes and 64 kilobytes. For more information about the size limit of different types of I/O performed by SQL Server, visit http://blogs.msdn.com/b/sqlcat/archive/2005/11/17/493944.aspx

To monitor your database log buffer flush and it's size, refer the the perfmon counters given below:

SQLServer: Database object counter	Displays
Log Bytes Per Flush	Number of bytes in the log buffer when the buffer is flushed.
Log Flushes/sec	Number of log flushes per second.
Log Flush Wait Time	Total wait time (milliseconds) to flush the log.
Log Flush Waits/sec	Number of commits per second that are waiting on log flush.

I want to share one interesting observation which might help you to choose IMPLICIT or EXPLICIT Transaction:

During a troubleshooting scenario where INSERT'ing 60000 records to a 2 column sample table took 8 minutes on a specific server while the same operation takes ,< 20 seconds in two other identical server. I was in a position to proove that it is a Disk problem and not a configuration issue within SQL Server. This insert was running in a WHILE loop without any EXPLICIT Transaction.

When I looked into his Wait Stats (sys.dm_os_wait_stats), WRITELOG was the top wait type in terms of total wait time.

I explained about the WRITELOG wait type and conditions when LOG Blocks will be flushed to disk to Cx.

To show a demo about difference between IMPLICIT and EXPLICIT transaction which changes the behavior of Log Flush, changed his code:

Before:

WHILE @i < 60000

Begin

INSERT INTO table values (1,'Name')

End

After:

BEGIN TRAN

WHILE @i < 60000

Begin

INSERT INTO table values (1,'Name')

End

COMMIT TRAN

To my wonder, second batch with EXPLICIT transaction completed in just 2 seconds (compared to 8 mins when IMPLICIT transaction) in the same problematic environment.

Then it made sense to the customer that instead of 60,000 I/O, we just be sending <10 I/O which makes transaction to complete fast.

DMV sys.dm_tran_database_transactions also helped me a lot in proving the WRITELOG wait time for every transaction begin time. Also sys.dm_io_pending_io_requests helped me to show the outstanding I/O and whether the I/O is still pending and how long it is pending.

So to summarize,

If you see WRITELOG wait type, before blaming disk, check how frequently you transactions commits and how good is your disk response time and tune them accordingly.

Feel free to post your comments if you have any questions or if you need more information.

Further Reads:

http://blogs.msdn.com/b/psssql/archive/2011/01/07/discussion-about-sql-server-i-o.aspx

http://msdn.microsoft.com/en-us/library/cc917726.aspx

http://blogs.msdn.com/cfs-filesystemfile.ashx/__key/CommunityServer-Components-PostAttachments/00-09-98-45-35/Microsoft-SQL-Server-IO-Internals.pptx

Sakthivel Chidambaram, SQL Server Support

Kate Hudson Adriana Lima Brittany Daniel Kate Moss Zhang Ziyi