WRITELOG wait types - what your SQL Server is saying?
Whenever the SQL Server is struggling to write the transactions to the disk, you would observe WRITELOG wait type within SP_WHO2 results. So when you observer such wait types then you should be worried on the disks performance, either you plan for shrinking the transaction log (which is a temporary workaround) or add a second log file on a different disk.
The reason behind for this wati type is heavy rollback activity adds additional log and disk I/O contention, and such activity has been observed as contributing to writelog timeouts. For best performance, attempt to minimize the number of transactions that must be rolled back; as activity on the server increases, using a query timeout that is too short may further exacerbate the problem by causing unnecessary rollbacks.
To identify disk bottlenecks using counters, Profiler, ::fn_virtualfilestats, and Showplan.
Any of the following will reduce these waits:
- Adding additional I/O bandwidth.
- Balancing I/O across other drives.
- Placing the transaction log on its own drive.
So with such performance montiroing may be you find heavy I/O over one disk and low over other one. For the true terms of balancing the I/O drives you have to refer the hardware documentation for optimum performance.
If you plan to shrink the transaction log then it would help to reduce this WRITELOG wait type percentage, but it may not be the final solution as other transactions will addup more space to the transaction log if a proper backup strategy is not in place. Further on the SYSMON counters you can take help of DMV for further analysis, you can use sys.dm_os_performance_counters to peak at those System Monitor counters if you want to do all your analyis using T-SQL:
select DB_NAME(database_id) DB_NAME, file_id,io_stall_read_ms ,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
,io_stall_read_ms + io_stall_write_ms as io_stalls
,num_of_reads + num_of_writes as total_io
,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
order by avg_io_stall_ms desc