Blog Post

SQL Server –WRITELOG and how to reduce it

,

SQL Server Books online defines the wait type WRITELOG as “Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits. “

A checkpoint writes all SQL dirty pages , currently in the buffer,  onto disk. Transaction commits make  data modifications permanent in the database.

A few things to note:

1) Top priority – check for disk io susbsystem performance.

2) The sys.dm_io_virtual_file_stats DMV  assists in identifying slow performance by returning statistics on IO for the database files.

io_stall on sys.dm_io_virtual_file_stats is  a good indicator of IO issues. There is no definite number , but the lower the better.

Benchmark some systems on relevant workload – and use as a guide.

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'SQLSERVERDB'), 2);

3) If transaction logs are sharing the same drives as data then separate out onto different drive \ mapped to a different IO channel.

4)It’s not just slow IO subsystems relative to workload but also overworked IO subsystems that can be the cause

See Also

Calculate disk IO throughput and MB per second

SQL Server IO patterns and RAID levels

Disk IO performance , disk block size tuning and SQL Servers

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating