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.  




Array

No comments yet... Be the first to leave a reply!