Earlier I wrote two blog posts on Delayed Durability. Please read them before continuing this example, as they will give you good information about this concept before we start.
- SQL SERVER – Basics of Delayed Durability in SQL Server 2014
- SQL SERVER – How to Setup Delayed Durability for SQL Server 2014?
Now in this example we will see a working example of the same.
First, we will create a same database with the named DelayedDurability and once we create the same we will set the delayed durability to allow. Right after that we will create a dummy table inside it. Next we will create two stored procedures where one will have a delayed durability enable and one without delayed durability. We will see how much time each of the stored procedure takes to complete the task.
Task 1: Create database with delayed durability
-- Script for Delayed Durability
USE MASTER
GO
-- Create New Database
CREATE DATABASE [DelayedDurability]
GO
BACKUP DATABASE [DelayedDurability] TO DISK = 'NUL';
GO
-- Let us set Delayed Durability at DB level
USE [master]
GO
ALTER DATABASE [DelayedDurability] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO
Task 2: Create a dummy table
-- Creating Dummy table
USE [DelayedDurability]
GO
CREATE TABLE DummyTable
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
SALARY VARCHAR(100))
GO
Task 3: Create two Stored Procedures
The first stored procedure is created without delayed durability enabled.
-- First SP with Delayed Durability OFF
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() ) [Simple_Insert in sec]
END
GO
The second stored procedure is created with delayed durability enabled.
-- Second SP with Delayed Durability ON
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()) [DelayedDurability_Insert in sec]
END
GO
Task 4: Execute both the stored procedure and note down the result.
-- Now Execute both the SPs and check the time taken.
EXEC Simple_Insert
GO
EXEC DelayedDurability_Insert
GO
Result of SPs
Task 5: Clean up
-- Clean up
USE MASTER
GO
DROP DATABASE [DelayedDurability]
GO
Analysis of Result:
The SP/Query which ran with delayed durability ON performed better than other SP/Query which had delayed durability disabled. This is because we are executing a procedure which generated lots of log file and after a while query has to slow down if it is writing down the log file first before executing next set of instructions. However, if delayed durability is ON, the query will continue executing without worry of the log writing. This will improve performance but due to any reason there is unexpected shutdown, the data loss can happen.
You can learn more about the new features of SQL Server 2014 in my latest Pluralsight Course over here.
Note: Please do not enable delayed durability thinking will improve performance. I suggest you read my two blog posts listed over here and here before implementing it.
Reference: Pinal Dave (https://blog.sqlauthority.com)
