Delayed durability is a feature introduced in SQL Server 2014 that allows you to control the durability of transactions. By enabling delayed durability, you can improve the performance of write-intensive applications by reducing the overhead of writing data to disk.

Methods to Enable Delayed Durability

There are three methods to enable delayed durability in SQL Server:

Method 1: Database Level

You can enable, disable, or force delayed durability at the database level. To enable delayed durability for a database, you can use the following command:

USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = ALLOWED
GO

If you want your changes to take immediate effect, you can use the WITH NO_WAIT option:

USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO

There are three different options for the SET DELAYED_DURABILITY command:

• Disabled: This is the default setting and provides full transaction durability.
• Allowed: This option allows each transaction to decide whether to use delayed durability. The durability of each transaction will be based on the transaction level settings.
• Forced: This option forces each transaction to use delayed durability.

To ensure that each transaction follows delayed durability, you can execute the following statement:

USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = FORCED
GO

You can disable delayed durability by executing the following statement:

USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET DELAYED_DURABILITY = DISABLED
GO

You can also change these values from SQL Server Management Studio (SSMS).

Method 2: Transaction Level

If you have enabled database level transactions, you can specify the transaction level durability on the commit statement. However, if you have not enabled database level transactions, specifying transaction level durability will have no impact. Here is an example of how to specify transaction level durability:

COMMIT TRANSACTION nameoftransaction WITH (DELAYED_DURABILITY = ON);

Method 3: Natively Compiled Stored Procedure

You can also use delayed durability settings for natively compiled stored procedures. Here is an example of the syntax:

CREATE PROCEDURE <procedureName> ...
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
DELAYED_DURABILITY = ON,
...
)
END

By understanding and utilizing delayed durability in SQL Server, you can optimize the performance of your write-intensive applications without compromising data integrity.

That’s all for today. Stay tuned for more SQL Server tips and tricks!