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.
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
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)
You have the option of using this feature in the stored procedure too.
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.
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.