WRITELOG Wait

The WRITELOG Wait generally indicates that some task is waiting on the contents of the SQL Server transaction log to be written from memory to disk.  This write occurs when a transaction is committed.

When WRITELOG is the largest wait type on your system that may be an indication that there is more data being changed than typical. Why are so many things having to write to the log.

Fixing the problem

  • Increase I/O on the disk subsystem where the transaction log is being written.
  • Sometimes reducing the size of the transaction log helps.
  • Sometimes committing too often on a large batch can cause this.  Look for batches with too many commits.

Actual Customer Story:

I worked with a client that was having lots of WRITELOG waits, and they had a query running frequently that was doing something like this.

UPDATE t
SET t.name = x.name
FROM onetable t
INNER JOIN anothertable x on t.id = x.id;

This always did the update, even if it is updating the same value the data pages and log files still get written to.

The way we fixed it was to add a where clause to only update if it was different.

UPDATE t
SET t.name = x.name
FROM onetable t
INNER JOIN anothertable x on t.id = x.id
WHERE t.name <> x.name;

That change went from updating millions of rows every few minutes to instead updating only one or 2 rows every hour or so when something actually changed. WRITELOG was no longer the top wait based on that one update statement.


Enroll Today!
SteveStedman5
SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

To prove you are not a robot: *