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.

SQL Server 2014 is having a lot of useful feature and some are hidden as well. I was able to learn about “delayed durability” feature while preparing my session at GIDS. Since the session is over now, it’s right time to write about this feature. First, let’s understand durability.

In general, every transaction in relational database should follow ACID properties. Atomicity, Consistency, Isolation and Durability.

In SQL Server, durability is maintained by writing log record of modification to transaction log “synchronously”. If write to the disk is slow (or huge data to be written) the total time of transaction would become longer and we would see WRITELOG wait for the SPID. Once log record is hardened to transaction log, client would get commit confirmation.

SQL Server 2014 gives DBA and Application team to achieve durability with a delay. Delayed durability may give you some performance gain at the cost of durability factor of a transaction. It is achieved by writing the log record asynchronously. Log record is still generated and kept in memory area called “Log Cache”. Only difference is that it’s written to disk with the delay. So, even if application has received confirmation from SQL about the commit, it’s not actually hardened to disk. This is what is called as “delayed” durability.  If there is a unexpected failure of SQL before the data is written to disk, the data is essentially lost.

I must emphasize the fact that if your application can’t afford any data loss, don’t use this feature. (I don’t have option to put red flashing light otherwise I would have done that to stress on the data loss factor). Does this mean that you have no control about the flush of the log records from log cache to disk? No, product team has thought of this and provided a extended stored procedure called sp_flush_log. I would talk more about this later.

Now since you know the fundamentals of delayed durability, let’s see how to implement it. This can be controlled at various level. We can set delayed durability at Database Level, Transaction Level and in-memory stored procedure.

Database Level

We can change the setting using SSMS as below

Same options are provided in T-SQL as well.

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = DISABLED

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = ALLOWED

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = FORCED

If we set the value to disabled at database level, it would take highest priority. This means that transaction level setting would not be honored.

Transaction Commit Level

During commit of a transaction we can specify delayed durability option as ON or OFF

COMMIT TRAN [ transaction name | @transaction name variable ] [ WITH ( DELAYED_DURABILITY = { OFF | ON })]

Natively Compiled Stored Procedure Level

To know more about natively compiled procedure, I would like you to read here. While creating natively compiled procedure, we can specify delayed durability as below.

CREATE PROCEDURE SQLServerHelp
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH 
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
    …
)
END

Here is the quick chart which explains effect of settings done at database level and transaction level.

In next part, we would see delayed durability in action.