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.

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.

Using SQLQueryStress to generate a workload on SQL Server

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.

Adding Log Flushes/sec counter  to Performance Monitor

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.

The output of the Performance Monitor

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.

  • 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

How to change delayed durability option in SSMS

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

Delayed durability options that affect the performance of the SQL Server transaction log

Now we will change the DemoDb database delayed durability option as 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.

We start the SQLQueryStress with the following settings.

Using SQLQueryStress to create a workload

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:

Delayed durability option affects performance.

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.

  1. Press Cntr+F and Add a filter for the SQL Server process

    Adding an SQL Server filter to Process Monitor

  2. Add a filter to only monitoring the database log file that we will use. Then, we will click OK

    Adding an SQL Server transaction log file filter to Process Monitor

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

    The output of the Process Monitor for Full transaction durability

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

    SQL Server transaction log performance architecture

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.

Esat Erkec
Latest posts by Esat Erkec (see all)

Author: admin

Leave a Reply

Your email address will not be published.