Reduce SQL Server WRITELOG waits using Delayed Durability

By: Chandresh Patel   |   Updated: 2017-10-27   |   Comments (2)   |   Related: > Performance Tuning

Problem

In SQL Server, if we have a transactional based system and find a high WRITELOG wait type this is a performance bottleneck and can cause the transaction log file to grow rapidly and frequently. To troubleshoot this problem, it requires gathering some data regarding the current server load and analyzing the data to identify the actual cause of the bottleneck. In our case, for the WRITELOG wait type we need to review disk performance and queries that are currently waiting on WRITELOG.

Solution

In order to solve a high WRITELOG wait type, using DELAYED_DURABILITY may be fruitful.  This option was introduced in SQL Server 2014. If it seems like a disk performance related issue, it does not always mean adding faster hardware will fix this issue, so you need to be open to other ideas to solve performance problems.

SQL Server Delayed Durability

Delayed Durability is also known as transaction durability and has two configuration options:

1. Full transaction durability
2. Delayed transaction durability

Let me give the basic details about these options.

1. Full transaction durability in SQL Server

Fully durable transaction commits are synchronous and this ensures the transaction is written to the log file before the transaction completes.  This is the default behavior.

Use Full Transaction Durability when you cannot bear data loss, but note that in a catastrophic event, like a server crash, there still is a possibility of some data loss.

2. Delayed transaction durability in SQL Server

Delayed transaction commits are asynchronous.  The transaction is written to a buffer and then written to disk when the buffer fills or you use sp_flush_log.  This option reduces the latency of transactions, so they appear to finish quicker to the end user.  This option is helpful if you have a bottleneck writing to the log.

Use Delayed Transaction Durability if you have a bottleneck writing to the log file and you can afford some possible data loss if there is an issue writing from memory to the log file.  Also, just like above in a catastrophic event, like a server crash, there is a possibility of some data loss.

SQL Server Transaction Durability Demo

Create new database and issue a backup:

USE MASTER
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE [name] = 'TTESTT_DB')
BEGIN
     EXEC msdb.dbo.sp_delete_database_backuphistory
          @database_name = N'T-TESTT_DB'

     ALTER DATABASE [TTESTT_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

     DROP DATABASE [TTESTT_DB]

END
GO

CREATE DATABASE [TTESTT_DB]
GO

BACKUP DATABASE [TTESTT_DB] TO DISK='NUL:'
GO 

In the new database I have created a table to insert data:

USE [TTESTT_DB]
GO

CREATE TABLE InsertDataTable (
EmpID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
EmpLicenceCode VARCHAR(256)
)
GO 

Following is an ALTER statement to allow DELAYED_DURABILITY at the database level:

USE [MASTER]
GO

ALTER DATABASE [TTESTT_DB] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO 

Below is a stored procedure which will COMMIT the transactions using full durability and insert 50,000 rows into the table:

USE [TTESTT_DB]
GO
CREATE PROCEDURE InsertWithDelay_Off
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @LoopCount INT= 0

     WHILE (@LoopCount < 50000)
     BEGIN
          BEGIN TRAN
               INSERT INTO InsertDataTable VALUES (@LoopCount)
               SET @LoopCount = @LoopCount + 1
          COMMIT WITH (DELAYED_DURABILITY = OFF)
     END
END
GO

Here is a second stored procedure which will COMMIT the transactions using delayed durability and insert 50,000 rows into the table:

USE [TTESTT_DB]
GO
CREATE PROCEDURE InsertWithDelay_On
AS
BEGIN
     SET NOCOUNT ON
     DECLARE @LoopCount INT= 0

     WHILE (@LoopCount < 50000)
     BEGIN
          BEGIN TRAN
               INSERT INTO InsertDataTable VALUES (@LoopCount)
               SET @LoopCount = @LoopCount + 1
          COMMIT WITH (DELAYED_DURABILITY = ON)
     END
END
GO

Let's see what happens when we execute these stored procedures.

CASE 1: Full Transaction Durability in SQL Server

We will open Activity Monitor in SSMS and then run the following T-SQL code.

USE [TTESTT_DB]
GO
DECLARE @dtDATE DATETIME = GETDATE()
EXEC InsertWithDelay_Off
SELECT DATEDIFF(ms, @dtDATE,GETDATE())/1000 AS TimeInSecond
GO

We can see below this took 8 seconds to complete.

Image 1: Execution time taken by stored procedure InsertWithDelay_Off

If we look at Activity Monitor in SQL Server Management Studio we can see the Resource Waits for Logging and the wait time is quite high.

Image 2: Activity Monitor

CASE 2: Delayed Transaction Durability in SQL Server

We will run the following code that commits the transactions using delayed durability.

USE [TTESTT_DB]
GO
DECLARE @dtDATE DATETIME = GETDATE()
EXEC InsertWithDelay_On
SELECT DATEDIFF(ms, @dtDATE,GETDATE())/1000 AS TimeInSecond
GO

We can see this now only took 1 second to complete.

Image 3: Execution time taken by stored procedure InsertWithDelay_On

If we look at Activity Monitor we can see the Resource Waits for Logging is much less than the first example.

Image 4: Activity Monitor

Summary

When we compare the results of both cases with delayed durability enabled and with delayed durability disabled, we can see a huge impact on disk IO in the “Data File I/O" pane and the resource waits in the "Resource Waits" pane.

Please note that screenshot 2 and 4 were taken while the processes were running, so it could vary when you run it.

Next Steps

• Please do not implement this in your production servers without testing and without understanding business requirements.
• Additional resources:
  • Control Transaction Durability
  • What is WRITELOG wait type

About the author

Chandresh Patel is currently a Senior SQL Developer and DBA focusing on query tuning, optimization and administration with SQL Server 2005/2008/2012/2014.

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

View all my tips