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
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
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
- DISABLED – This is the default durability
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