Reduce SQL Server WRITELOG waits using Delayed Durability
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.
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:
- Full transaction durability
- 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
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.
- Please do not implement this in your production servers without testing and without understanding business requirements.
- Additional resources:
Last Updated: 2017-10-27
About the author
View all my tips