Improve SQL Server transaction log performance with Delayed Durability

In this article, we will learn the Delayed Durability feature that helps to improve transaction log file write throughput in SQL Server.

OLTP (Online Transaction Processing) databases should process a huge number of transactions within the shortest time and concurrently. Therefore, the transaction completion time becomes more important for the performance of the OLTP databases. Particularly for SQL Server, the transaction log (T-log) file configuration will play a key role in the performance of the transaction completion times because the write throughput to the log file directly affects the application response times.

What is the write-ahead logging (WAL)?

In the ACID (atomicity, consistency, isolation, durability) databases, the write-ahead logging mechanism guarantees that the modifications are first recorded into the log files before the transactions are committed. So, this mechanism solves the following matters:

Offers the rollback process to be completed properly
Ensures log files are created, so we able to reach and restore the modification history records to resolve unexpected errors

SQL Server uses the write-ahead logging mechanism but a little bit different than the above description. SQL Server does not write the modification transaction directly into the transaction log files. Firstly, it stores the modifications in a small memory area whose name is log buffer. The maximum capacity of this area is 60 KB. The log buffer is flushed into the disk in certain conditions.

When the transaction is committed
When the log buffer fills up, it means that it reaches the 60KB
When the sys.sp_flush_log is executed
CHECKPOINT process is completed

After all these explanations, the databases which involve a huge amount of small transaction workload may experience write latency issue which is related to the log file. In the next sections, we will learn how to solve this problem.

Simulate a workload on SQL Server transaction log file

In this section, we will generate a synthetic workload for a log file and will observe the behavior of the write-ahead logging principle of the SQL Server. At first, we will create the following database and table to use our workload tests.

USE MASTER

CREATE DATABASE [DemoDb]

ALTER DATABASE [DemoDb] SET DELAYED_DURABILITY = DISABLED

USE [DemoDb]

CREATE TABLE TestWorkload

(ID INT PRIMARY KEY IDENTITY(1,1) , RandomText VARCHAR(100))

After creating the table, we will generate a workload with the help of the SQLQueryStress. We are going to paste the following query into the query pane of the SQLQueryStress and set the Number of Iterations and Number of Threads

The Number of Iterations setting helps to specify how many time the query will be executed
The Number of Threads setting helps to specify the number of concurrent user

We will set the Number of Iterations as 200 and the Number of Threads as 5 so the total number of the query that will be executed is 1000. The following query will insert sample rows into the TestWorkLoad table in an explicit transaction. Another point about this query is that we will use a WHILE loop statement. This control-flow enables us to generate a 1000 transaction recursively as fast as possible. So that this query will create a workload on the log file of the database. Now let’s start the SQLQueryStress.

DECLARE @Counter AS INT=0

WHILE @Counter <=1000

BEGIN

SET @Counter=@Counter+1

BEGIN TRAN

INSERT INTO TestWorkLoad VALUES('Bla Bla Text')

COMMIT

END

However, before starting the SQLQueryStress, we will start the Performance Monitor to observe the Log Flushes/sec bytes counter. This counter represents how many numbers of the log buffer flushes into the transaction log file per second.

When we analyze the Perfmon graph, after completion of the query executions. During the execution of the queries, SQL Server flushes the log buffer into the transaction log files. This value is 20 in the lowest place as we observe. This working principle is called Full transaction durability and it synchronously writes the transaction into the transaction log files after the commit statement. The biggest advantage of transaction durability type is minimizing the data loss. Against this advantage, it creates writing overhead on the log file.

Delayed transaction durability

The delayed transaction durability feature was announced with SQL Server 2014 and it aims to reduce the write overhead on the log files. In this setting, the transaction is written into the memory buffer for a while and then written into the log file. It means that the commit statement does not trigger to log flush operation. On the other hand, the disadvantage of this setting is, it can be caused to data loss any SQL Server memory issues. At the database level, we can use the following options to enable the delayed durability options.

1	ALTER DATABASE Databasename SET DELAYED_DURABILITY = { DISABLED \| ALLOWED \| FORCED }

DISABLED is the default setting and in this setting, all transactions will be fully durable However, we can change this behavior at the commit level of the query
In the ALLOWED option, each transaction’s durability is determined at the transaction level
In the FORCED option, all transactions will be delayed durable

At the same time, we can change the delayed durability option with the help of the SQL Server Management Studio (SSMS). At first, we launch the SSMS and right-click on the database and select the Properties menu

We click the Options tab. Under the Miscellaneous setting group, we can find out the Delayed Durability option.

Now we will change the DemoDb database delayed durability option as ALLOWED.

1	ALTER DATABASE [DemoDb] SET DELAYED_DURABILITY = ALLOWED

We will execute the following query. In this query, we will set the delayed durability at the commit level. To do this, we will add DELAYED_DURABILITY = ON syntax after the COMMIT statement.

DECLARE @Counter AS INT=0

WHILE @Counter <=1000

BEGIN

SET @Counter=@Counter+1

BEGIN TRAN

INSERT INTO TestWorkLoad VALUES('Bla Bla Text')

COMMIT WITH (DELAYED_DURABILITY = ON)

END

We start the SQLQueryStress with the following settings.

At the same time, we will continue to track the Perfmon graph after starting the query execution. The output of the Perfmon will be as shown below:

As we can see the log flush number has reduced. When we compare the full durable and delayed durable execution times, the delayed durable transaction acts a better performance.

Query Type	Completion Time (second)
Delayed Durability option: OFF	54
Delayed Durability option: ON	48

Behind the scene of the delayed transaction durability

With the help of the Process Monitor, we can observe the writing operations that affect the transaction log files. After launching the Process Monitor, we will filter the SQL Server process and the log file that will be written.

Press Cntr+F and Add a filter for the SQL Server process
Add a filter to only monitoring the database log file that we will use. Then, we will click OK

We will execute the following query and monitors the Process Monitor report

DECLARE @Counter AS INT=0

WHILE @Counter <=1000

BEGIN

SET @Counter=@Counter+1

BEGIN TRAN

INSERT INTO TestWorkLoad VALUES('Bla Bla Text')

COMMIT WITH (DELAYED_DURABILITY = OFF)

END

We will execute the following query and monitors the Process Monitor report

DECLARE @Counter AS INT=0

WHILE @Counter <=1000

BEGIN

SET @Counter=@Counter+1

BEGIN TRAN

INSERT INTO TestWorkLoad VALUES('Bla Bla Text')

COMMIT WITH (DELAYED_DURABILITY = ON)

END

As we have seen that in the full transaction durability mode the transactions have been written to the log file at the same sizes and number of the commit statement. Despite that, in the delayed transaction durability mode the transactions have been written to the log file in the random sized chunks and not equals the number of the commit statement.

Delayed transaction durability advantages and disadvantages

The delayed durability feature reduces the transaction commit times and it also reduces the log file write latency. Besides, it might resolve the WRITELOG wait type problem.

On the other hand, the possible data loss option always threatens us when we use this feature. So if we have data loss tolerance and we need better performance for the data modification operations, we can decide to use it.

Conclusion

In this article, we discovered a new feature that helps to improve the performance of the transactions. Delayed transaction durability changes the default write-ahead logging mechanism of SQL Server and keeps the transactions in the memory after the commit operations. This approach reduces the writing overhead of the transaction log files. As we stated, in order to use this option, we have data loss tolerance.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

What is the write-ahead logging (WAL)?

Simulate a workload on SQL Server transaction log file

Delayed transaction durability

Behind the scene of the delayed transaction durability

Delayed transaction durability advantages and disadvantages

Conclusion

Related posts: