Troubleshooting SQL Server Transaction Log Related Wait Types

By:   |   Comments (1)   |   Related: > Performance Tuning


Problem

In my previous tip, First Steps on Performance Troubleshooting, I suggested that when facing a performance problem you should take a look at the transaction log. In this tip I will show you how you can use transaction log related wait types to perform a diagnosis.

Solution

Troubleshooting transaction log issues can be difficult due to the fact that there are several different aspects that affect the transaction log and therefore can downgrade its performance. The causes vary from a hardware issue to an improper configuration, which is the most frequent cause. In both cases, the problem will be reflected in the wait types. But by looking at the wait types alone won’t give you the answer per se. As a suggestion, I would recommend that in addition to looking at the wait types you should also look at the workload that generates the waits.

Let’s suppose two different scenarios; the first, a database where it’s running a bulk insert and the second, a database where it is running an OLTP workload. In both cases the possible transaction log related wait types could be the same. What I mean is that in some scenarios the presence of these wait types are most likely to occur and won’t be a major problem.

The Starting Point

Before digging into the transaction log wait types we should start with the basics. The transaction log files should be on a separate disk other than the data files for dedicated SQL Servers. I have seen some SQL Server installations that use a partitioned disk, but this is the same as having the data and log files on the same drive (in fact they are!) with the addition that the disk head has to move more because of the distance amongst the partitions. The recommendation is to keep database log files on a separate RAID 10 drive, when possible.

LOGMGR_QUEUE wait type

According to Microsoft documentation, this wait type occurs when a task is waiting for any outstanding log I/Os to finish. If you see this wait type you should not be worried because it indicates that the log writer is waiting to be invoked by a session or the checkpoint process. In other words, it is telling you that there is no current log activity. It’s most unlikely to see this wait type on user sessions because the log writer is a background process.

LOGMGR wait type

This wait type is seen when a database is being closed, either when a database is set offline or read only or when the instance is shutting down. In the last case you will only see this wait type if you connect through a Dedicated Administrator Connection (DAC).

LOGMGR_RESERVE_APPEND wait type

This wait type occurs when a task is waiting to see if log truncation frees up log space to allow the task to write a new log record. If you see this wait type you should consider increasing the log size and if the log file has the auto grow property enabled you can increase its value.

If you opt for increasing the auto grow value and not increase the log file size, you have to consider that the instant file initialization feature does not work for log files, so the best you can do is to calculate the required log space and allocate it at once.

After taking these actions, if the problem persists you may have to take a look at the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view to check what is delaying the log truncation process.

LOGBUFFER and WRITELOG wait types

Now I will explain that the LOGBUFFER and WRITELOG wait types that are very similar, but not equal. Although in the practice to troubleshoot them the approach is mostly the same, there is a marked conceptual difference amongst them.

According to Microsoft, the LOGBUFFER wait type occurs when a task is waiting for space in the log buffer to store a log record, and the WRITELOG wait type occurs while waiting for a log flush to complete. In order to understand the previous definitions, first we need to know what the log buffer is.

The Log Buffer is a small contiguous area of memory where SQL Server stores the log records before writing them into the transaction log file in a process called Log Flush. When SQL Server is processing a transaction that inserts, updates or deletes data, or changes the physical structure of the database, it checks if the data page that is going to be modified is already in memory, otherwise the engine fetches the page from disk into memory. When the page is in memory, SQL Server changes its content in memory and marks the page as “Dirty”. At the same time the database engine creates a new log record in the Log Buffer. At this point is when the LOGBUFFER wait type takes leadership, because it measures the time it takes to create a new log record into the log buffer.

In order to comply with the ACID (Atomicity, Consistency, Isolation, and Durability) principle, SQL Server needs to flush the log buffer (write the log buffer contents into the log file) before writing the dirty data pages into the datafiles. And here is when the WRITELOG wait type arises. It measures the amount of time being spent writing the log buffer contents into the transaction log.

Now that we know the difference between these two wait types we are ready to deduce what may cause them.

Due to the fact that the log buffer is a small portion of memory, a transaction that modifies a big amount of data and fills the log buffer, it will need to be flushed in chunks into to the log file. Also a long running transaction that has log records in the log buffer will release less log buffer space to other transactions that must wait for log buffer space in order to keep going. Here we can see a correlation between these two wait types, because if we think about it the log buffers will be waiting to be flushed (LOGBUFFER wait type) if and only if the log records cannot be written to the transaction log (WRITELOG wait type).

You may think why didn't the Microsoft engineers make the log buffer bigger, imagine the following scenario. Your company deposits your salary into your bank account, the transaction is committed but not stored in the log file and the bank server has a power failure. Since the transaction was committed, you have been paid, but when the bank database is recovered without that transaction in the log file, your deposit will be lost.

The question is what can cause these wait types and how to troubleshoot them. The first thought that comes to mind is to check the disk response time. You can do so by querying the IO related Dynamic Management View sys.dm_io_virtual_file_stats and check the values of io_stall_write_ms and io_stall_queued_write_ms for the log file. Also you can check the performance counters PhysicalDisk:Avg.Disk Queue Length and PhysicalDisk :Disk Write Bytes/sec.

If you don’t see any lag on disk activity, you can check the number of virtual log files (VLF). The higher the VLF count the slower the log performance will be. You can check the number of VLFs with the DBCC LOGINFO database control command.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, February 4, 2016 - 1:21:53 PM - Iván Back To Top (40598)

Thank you very mucho, your work is great!!!!

 















get free sql tips
agree to terms