Delayed Durability is a feature introduced in SQL Server 2014 that allows you to improve the performance of write operations by delaying the durability of the transaction log. In this blog post, we will explore the concept of Delayed Durability and see how it can impact the performance of your SQL Server database.

What is Delayed Durability?

Delayed Durability is a database-level setting that allows you to control the durability of write operations. By enabling Delayed Durability, you can defer the writing of transaction log records to disk, which can significantly improve the performance of write-intensive workloads.

When Delayed Durability is enabled, SQL Server will continue executing queries without waiting for the transaction log to be written to disk. This can result in faster execution times for write operations, as the system does not have to wait for the disk I/O to complete.

How to Enable Delayed Durability?

To enable Delayed Durability for a database, you can use the following T-SQL command:

ALTER DATABASE [YourDatabaseName] SET DELAYED_DURABILITY = ALLOWED;

Once enabled, you can specify the level of Delayed Durability for individual transactions by using the WITH (DELAYED_DURABILITY = ON) or WITH (DELAYED_DURABILITY = OFF) options in your queries or stored procedures.

Impact on Performance

The performance impact of Delayed Durability can vary depending on the workload and the specific queries being executed. In general, enabling Delayed Durability can improve the performance of write-intensive operations, as the system does not have to wait for the transaction log to be written to disk.

However, it is important to note that enabling Delayed Durability does come with a risk of data loss. If there is an unexpected shutdown or failure, any transactions that have not been written to the transaction log will be lost. Therefore, it is crucial to carefully consider the trade-off between performance and data durability when deciding whether to enable Delayed Durability.

Example

Let’s take a look at a simple example to understand the impact of Delayed Durability on performance. In this example, we will create a database called “DelayedDurability” and compare the execution times of two stored procedures – one with Delayed Durability enabled and one without.

-- Create database with delayed durability
USE MASTER;
CREATE DATABASE [DelayedDurability];
GO

-- Enable Delayed Durability at DB level
USE [master];
ALTER DATABASE [DelayedDurability] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT;
GO

-- Create a dummy table
USE [DelayedDurability];
CREATE TABLE DummyTable (
    ID INT IDENTITY PRIMARY KEY CLUSTERED,
    SALARY VARCHAR(100)
);
GO

-- Create stored procedures
CREATE PROCEDURE Simple_Insert
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @counter AS INT = 0;
    DECLARE @start DATETIME;
    SELECT @start = GETDATE();

    WHILE (@counter < 100000)
    BEGIN
        BEGIN TRAN;
        INSERT INTO DummyTable VALUES (@counter);
        SET @counter = @counter + 1;
        COMMIT WITH (DELAYED_DURABILITY = OFF);
    END

    SELECT DATEDIFF(SECOND, @start, GETDATE()) AS [Simple_Insert in sec];
END
GO

CREATE PROCEDURE DelayedDurability_Insert
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @counter AS INT = 0;
    DECLARE @start DATETIME;
    SELECT @start = GETDATE();

    WHILE (@counter < 100000)
    BEGIN
        BEGIN TRAN;
        INSERT INTO DummyTable VALUES (@counter);
        SET @counter = @counter + 1;
        COMMIT WITH (DELAYED_DURABILITY = ON);
    END

    SELECT DATEDIFF(SECOND, @start, GETDATE()) AS [DelayedDurability_Insert in sec];
END
GO

-- Execute both stored procedures and note down the results
EXEC Simple_Insert;
EXEC DelayedDurability_Insert;
GO

-- Clean up
USE MASTER;
DROP DATABASE [DelayedDurability];
GO

Based on the results of the example, we can see that the stored procedure with Delayed Durability enabled performed better than the one without Delayed Durability. This is because the query with Delayed Durability does not have to wait for the transaction log to be written to disk, resulting in faster execution times.

Conclusion

Delayed Durability is a powerful feature in SQL Server that can significantly improve the performance of write operations. However, it is important to carefully consider the trade-off between performance and data durability when deciding whether to enable Delayed Durability. It is recommended to thoroughly test and analyze the impact of Delayed Durability on your specific workload before implementing it in a production environment.

Thank you for reading this blog post. If you have any questions or comments, please feel free to leave them below.