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
Republished with author's permission. See the original post here.
Republished from SQL Server DBA [65 clicks].
Read the original version here [32134 clicks].