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

 PREEMPTIVE_OS_WRITEFILEGATHER 

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

ASYNC_IO_COMPLETION

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

LOGBUFFER

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

 WRITELOG

Writing transaction log to disk

 

Method 4 : Check the Log_reuse_wait_desc in sys.databases

 NOTHING

CHECKPOINT

LOG_BACKUP

ACTIVE_BACKUP_OR_RESTORE

ACTIVE_TRANSACTION

DATABASE_MIRRORING

REPLICATION

DATABASE_SNAPSHOT_CREATION

LOG_SCAN

OTHER_TRANSIENT

 

 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 (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on Troubleshooting SQL Transaction Log performance


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer