Troubleshooting SQL Transaction Log performance

04 October,2013 by Jack Vamvas

Slow performance on SQL Transaction Logs   can cause  serious bottlenecks  if   throughput and latency is slow.   

Identifying the root cause requires some troubleshooting effort. Part of the troubleshooting includes gathering data , to give some clues.

These methods assist in gathering some information, which could give some clues for a solution or further investigation

Method 1 : Check the file latency within SQL Server using sys.dm_io_virtual_file_stats (db_id,file_id)

This script reports on the latency for each file:


select db_name(database_id), 
io_stall_read_ms/num_of_reads AS 'Disk Read Transfer/ms',
io_stall_write_ms/num_of_writes AS 'Disk Write Transfer/ms'
from sys.dm_io_virtual_file_stats (2,1)

 SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats

Method 2 : Query the sys.dm_os_waiting_tasks DMV, which reports on wait information at a task level. It’s an effective and accurate method for viewing the waits on a task

SQL Server – sys.dm_os_waiting_tasks and performance

Method 3 : Query the sys.dm_os_wait_stats DMV, which reports on wait information at a cumulative level grouped by wait type


Some typical wait types associated with Transaction Logs are


Long Autogrow events, try plan and manually grow data and log files’


Can be for "zeroing" out a transaction log file during log creation or growth.


Indicates worker thread is waiting for a log buffer to write log blocks for a transaction


Writing transaction log to disk


Method 4 : Check the Log_reuse_wait_desc in sys.databases












 Read More

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

How to troubleshoot a slow running query in SQL Server

Current queries executing on SQL Server

Find high impact queries with sys.dm_exec_query_stats  

Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Troubleshooting SQL Transaction Log performance | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer