Delayed Durability- Diminishes I/O throughput to improve performance in the SQL Query.

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jan 18, 2016

Delayed Durability- Diminishes I/O throughput to improve performance in the SQL Query!


It is very clear that you can get significant performance benefit from the Delayed Durability if you can tolerate Data loss. 


Introduction:


In this blog we will be discussing one of the important and useful features
of the SQL server 2014 - Delayed Durability. This makes use of an in-memory transaction log feature which delays writing transaction log entries to a disk and gives performance enhancement to an SQL query.

Let’s understand How Delayed transaction Durability works?

Delayed transaction Durability reduces the waiting time caused by I/O throughput on the database transaction log. With Delayed Durability the transaction log records are stored in the memory and written to the disk in batches which in turn reduces the I/O wait times. This process is also referred to as a lazy commit. A flush to disk can happen when:                         

1)    A fully durable transaction in the same database commits successfully

2)    The user executes the sp_flush_log system-stored procedure successfully

3)    The in-memory transaction log buffer fills up and then transactions are automatically flushed to a disk

 

How delayed transaction durability is used at the database level? 

We can control Delayed Durability at the database level by using an ALTER database statement as given below: 

ALTER DATABASE <DBName> SET DELAYED_DURABILITY = DISABLED | ALLOWED | FORCED; 

There are three options available at the database level when setting the option for a Delayed Durability:

Disabled: This is the default setting and very similar to a full transaction durability.

Allowed: This option allows you to set on or off Delayed durability at the transaction level.             

Forced: This allows all transactions to be used in a Delayed durability forcefully at the database level.

We can also check what option(Whether Delayed durability) is configured at database level by running below query.

SELECT sd.name, sd.recovery_model_desc, sd.delayed_durability_desc, FROM sys.databases AS sd INNER JOIN sys.master_files AS mf   ON sd.database_id = mf.database_id    WHERE mf.[type] = 1;

How we can use delayed transaction durability in stored procedure or Transactions? 

You can use FORCED option which will try to handle all transactions as a delayed durable, you can use ALLOWED which will let you use delayed durable transactions .You can specify as below

In Transaction

BEGIN TRAN

INSERT INTO table1 (myDate) SELECT GETDATE()

COMMIT WITH (DELAYED_DURABILITY = ON) 

In Stored Procedure 

CREATE PROCEDURE <Procedure_Name>

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
DELAYED_DURABILITY = ON,
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English'

)

Following system-stored procedure will flush all committed transactions those are in-memory log to the disk using the following command.

EXEC sp_flush_log

How Delayed Durability helps in performance issues and where and when should you use this….. 

Bottleneck on transaction log writes: when you are suffering from performance issues due to latency when writing to the transaction log. It reduces commit time 

A high contention workload: If your wait stat analysis shows that your performance slowness is caused by the delay in locks being released then it helps remove the contention as it reduces the commit time and such locks will be released faster (less blocking and higher quantity). 

Let’s look at the below scenario to execute the following query with and without Delayed Durability

 

USE DelayedDB

SET NOCOUNT ON

DECLARE @counter AS INT = 0

PRINT (CONVERT(VARCHAR, SYSDATETIME(), 121))

    WHILE (@counter < 1000000)

        BEGIN

             INSERT INTO t1Data (D1, D2)

            VALUES('Data', @counter)

             SET @counter = @counter + 1

         END

GO

GO

PRINT (CONVERT(VARCHAR, SYSDATETIME(), 121))

GO

The above query took around 540 seconds without Delayed durability set at the database level. But the same query took an average of 34 seconds this gives more than 10 times improvement in throughput with Delayed Durability Enabled.

 

An Avg. Disk Queue Length counter max value was 2.4 with Delayed Durability and 1.9 with full Durability while running above transaction. 

Avg. Disk Queue Length counters value was little high with Delayed Durability as transactions were pending in Memory and was lower with the Full Durability as fewer transactions were pending in memory written to disk.

Log Flushes/sec is normal with Full Durability and we can see little spike after executing sp_flush_Log stored procedure manually we can see little spike where log flush hardened to transaction log in performance monitor which shows delayed durability. 

Refer to the below screenshots for performance counter values.

Before executing transaction


delayed

 delayed1

After executing transaction


delayed2

 

delayed3

Risk of Enabling Delayed Durability?

In the case of a catastrophic event like a server crash you will lose the data for all committed transactions that have not been saved to the disk.

There is no difference between an unexpected shutdown and an expected shutdown/restart of SQL Server. In case of the catastrophic events you should plan ahead to avoid data loss.

Conclusion: It is very clear that you can get significant performance benefit from the Delayed Durability if you can tolerate Data loss. This feature is not limited to only enterprise edition but works for all available editions in SQL 2014. You should always test it entirely to be sure how much benefit it is providing according to your DML data workload before applying it in the production environment.

For any questions on the topic click below:  

Ask Anil