In part 1 of the post about delayed durability, we have learned the basics of delayed durability. In this part, lets see it in action. I must point out that this feature is also called “Lazy Commit”. Hope you remember that we can set durability as delayed at two levels, database level and transaction level (commit statement). The idea of this demo is to show difference in writing of transaction log record. I have used free tool called process monitor (free download here) which is very useful to profile file/registry/network related activity by processes.

Here is the script to create database and table.

USE master
go
IF DB_ID('MyDemoDB') IS NOT NULL
begin
ALTER DATABASE [MyDemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [MyDemoDB]
end
GO
-- Create New Database, set recovery to full and take full backup
CREATE DATABASE [MyDemoDB]
GO
ALTER DATABASE [MyDemoDB] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [MyDemoDB] MODIFY FILE ( NAME = N'MyDemoDB', SIZE = 500MB )
GO
ALTER DATABASE [MyDemoDB] MODIFY FILE ( NAME = N'MyDemoDB_log', SIZE = 500MB )
GO
BACKUP DATABASE [MyDemoDB] to DISK = 'MyDemoDB.bak'
GO

-- create a table. 
Use [MyDemoDB]
GO
create table DemoTable 
(col1 int identity primary key clustered, 
 col2 varchar(100))  
GO

We can use the combination specified below to do specific delayed durable or fully durable transaction

For demo purpose, I have selected database Setting = Allowed and transaction setting delayed_durability = OFF to achieve fully durable transaction. Below is the script to change and view the settings for database.

USE [master]
GO
ALTER DATABASE [MyDemoDB] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO
-- Verify setting
select [name], [database_id], [delayed_durability], [delayed_durability_desc]
from sys.databases where name = 'MyDemoDB'
GO

Here is the stored procedure where I am setting transaction level delayed durability to OFF = Fully Durable.

Use MyDemoDB
go
Create Procedure Insert_DemoTable
as
begin
SET NOCOUNT ON
 
DECLARE @counter AS INT = 0
DECLARE @start datetime
Select @start = getdate()
 
    WHILE (@counter < 100)
        BEGIN
            begin tran
             INSERT INTO DemoTable VALUES( @counter)
             SET @counter = @counter + 1
             commit with (delayed_durability = OFF)
         END
Select datediff(second, @start, getdate() )
end
go

Since our database setting is set to allowed, we will create another procedure to set delayed durability as ON on transaction level.

Use MyDemoDB
go
Create Procedure Insert_DemoTable_Delayed
as
begin
SET NOCOUNT ON
 
DECLARE @counter AS INT = 0
DECLARE @start datetime
Select @start = getdate()
 
    WHILE (@counter < 1000)
        BEGIN
            begin tran
             INSERT INTO DemoTable VALUES( @counter)
             SET @counter = @counter + 1
             commit with (delayed_durability = ON)
         END
Select datediff(second, @start, getdate() )
end
go

Now we are ready to perform some test to see effect on log record writing in fully durable and delayed durable transaction. I have started ProcMon and added filter (Ctrl+L) for “Path” as “E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\MyDemoDB_log.ldf” which is the transaction log file path for MyDemoDB database. You may want to change as per your environment. Here are the two pictures which can explain a lot by themselves.

Image 1: Parallel capture – SSMS and ProcMon showing the activity done by sqlservr.exe during fully durable transaction

Image 2: Parallel capture – SSMS and ProcMon showing the activity done by sqlservr.exe during delayed durable transaction

Here are the important points to note:

• In case of fully durable transaction, we are seeing 100 WriteFile calls to ldf file. This is due to the fact that in stored procedure, we are doing 100 transactions.
• When delayed durable transaction is performed, SQL Server didn’t write synchronously to transaction log file for each transaction.
• In delayed durable test, 100 transactions were clubbed together and only one writeFile is issued asynchronously.
• Size of write for each transaction was 4096 byte and there were 100 calls in fully durable transaction but in delayed durable, the size of write is 36864 and just one call.
• If we use undocumented command fn_dblog, we can see that the log record structure is same.

To test further, I have modified the procedure and increased the counter to 100000 and here is the time taken by them is shown below.

The test which I have done may not be pattern of your work load but this is just to show you performance gain for certain kind of workloads. This is available with ALL editions of SQL Server 2014 and NOT restricted to In-Memory OLTP (few people have asked me this!)

SQL Server 2014 has in-built extended stored procedure called sp_flush_log which can be used to forcefully flush the log buffers to transaction log. You can read more here

I have heard people explaining this feature as SQL not using Write Ahead Logging. That is totally incorrect. WAL is not about synchronous writing of transaction log, it’s about Writing in log ahead of data change. If WAL is broken, crash recovery of database would be a problem. I hope that this blog gives you an understanding about this new feature called Lazy Commit or Delayed Durability.