SQL Server 2014 – Delayed Transaction Durability

Prior to SQL Server 2014, SQL Server used a Write Ahead Log (WAL) mechanism which means that changes are written to the transaction log before they are committed.  The major advantage with this mechanism is that there will be no data loss . However, since IO is the slowest component, there will be a high IO contention for large writes especially if you have a low performing disk configuration for log files.

I created a sample database with following script.

CREATE DATABASE [DurabilityTest]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'DurabilityTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DurabilityTest.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'DurabilityTest_log', FILENAME = N'H:\DurabilityTest\DurabilityTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
    

In this scenario the H drive is a slow drive which will store the the log file.

A sample table is also created :

   CREATE TABLE tblTestDurability
( ID INT IDENTITY PRIMARY KEY CLUSTERED,
Name Char(200) )

Now, let us run a sample query which will insert 10,000 records.

DECLARE @I int = 9
WHILE @i < 10001
BEGIN
SET @i = @i +1
INSERT INTO tblTestDurability
(Name)
VALUES
('My Test ')
END

It took 14-18 seconds to execute this query. During the execution, WRITELOG wait type was experienced. Wait types were captured using the following query.

SELECT
owt.session_id,
owt.wait_duration_ms,
er.command,
owt.wait_type,
est.text
FROM
sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es ON
owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est
WHERE es.is_user_process = 1 --and es.session_id = 400
ORDER BY owt.session_id, owt.exec_context_id

In SQL Server 2014, there is now an option to change the durability which is called Delayed transaction durability. Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are stored in a buffer and written to disk later with this mechanism. The physical transaction log file is updated when the buffer is full or a buffer flushing event takes place. The delayed transaction durability option has two main advantages:

• The transaction commit processing does not wait for log IO to finish and return control to the client. This means transaction will be completed in less time than the previous versions.
• Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in larger chunks, reducing contention, and increasing throughput.

In SQL Server 2014, you can enable the Delayed Transaction Durability from the Options of the Properties dialog :

Let us assume this is set to Forced. Later in this article I will explain differences between the options.

Using the 'Forced' option, it took only 5-6 seconds to execute the same query and there was now visible WRITELOG wait.

Enabling Delayed Durability

There are three ways of enabling Delayed durability.

1. Database Level
   As shown in the above image, you can configure this in the database level following options.
   
   • DISABLED – This is the default durability option. In this there is no delayed durability.
   • ALLOWED – You can specify what transactions to have Delayed Durability option.
   • FORCED – Every transaction will have a Delayed Durability. Setting this option might be very dangerous. You can also set this using T-SQL :
     

     ALTER DATABASE DurabilityTest
     SET DELAYED_DURABILITY = ALLOWED
     

2. Transaction Level
   You can commit a transaction with Delayed Durability is set to on. In this situation, Delayed Durability is applicable only for that specified transaction.

   COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON)
   

3. Stored Procedure
   You have the option of using this feature in the stored procedure too.

Limitations

The main limitation of this feature is that there is a potential data loss.  By using sys.sp_flush_log, it will flush to disk the transaction log of the current database, thereby hardening all previously committed delayed durable transactions.

Conclusion

Delayed Transaction Durability is a new feature introduced with SQL Server 2014.  Though there is a potential data loss with this feature this feature can be utilized when there are high data inserts.  

]]>