It is very clear that you can get significant performance benefit from the Delayed Durability if you can tolerate Data loss.
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
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.
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
SET NOCOUNT ON
DECLARE @counter AS INT = 0
PRINT (CONVERT(VARCHAR, SYSDATETIME(), 121))
WHILE (@counter < 1000000)
INSERT INTO t1Data (D1, D2)
SET @counter = @counter + 1
PRINT (CONVERT(VARCHAR, SYSDATETIME(), 121))
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
After executing transaction
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: