Throughput improvement through Delayed Durability on COMMIT TRAN from SQL Server 2014 3

Durability is not a requirement of a relational database, you would term a database system as ACID compliant where the D in ACID is Durability, note – HBASE which sit’s upon HADOOP is ACID compliant! ACID applies to Transactions and not the prevailing database organisation method e.g. Relational, Key Value, Hierarchical etc.

Back to SQL Server, plain and simply – the Achilles heal of SQL Server scalability on writes is the transaction log, writes are synchronous, your final COMMIT TRAN waits until the log record is physically hardened off onto the storage medium.

IO sub-systems are inherently bad, especially SAN’s at small IO’s, if you are inserting, modifying individual rows then what is your row length? Likelihood is it’s small, transactions from multiple connections are batched up if they exist therefore making a bigger IO but the maximum IO size until SQL Server 2014 is 62KiBytes.

SQL Server 2014 introduces a new feature, that of “DELAYED_DURABILITY”, there are a number of ways it can be turned on – as an option on the COMMIT TRAN or forced for the entire database.

alter database delayed_durability_demo set delayed_durability = forced
alter database delayed_durability_demo set delayed_durability = allowed

By default the database has delayed_durability set to disabled.

When the option is set to allowed, you need to use the WITH ( DELAYED_DURABILITY = ON ) option on the COMMIT TRAN – note, it’s done on the commit!

If the database has delayed_durability set to disabled then any requests for DELAYED_DURABILITY e.g. as syntax on the COMMIT TRAN mentioned above will be ignored – it won’t error though which is a good thing imho.

Setting the option to Forced makes all writes to the transaction log having delayed durability i.e. the writes are asynchronous.

Example

To best illustrate the different options, let us see how many rows we can write in 60 seconds to see which method works the best from the scalability point of view – the SQL for the tests is shown at the end of the post.

Test #1: Each insert statement is committed as a single transaction.

Test #2: All inserts are committed as a single transaction.

Test #3: The WITH ( DELAYED_DURABILITY = ON ) is used but the database option is Disabled.

Test #4: Database Option DELAYED_DURABILITY = ON is set to FORCED.

Delayed Durability 60 Sec Ins Comparison

It is clear that inserting the rows in a single transaction is by far the best performer; taking advantage of DELAYED_DURABILITY gives a significant performance boost over the box standard Durable behaviour.

Now the word of extreme caution: delayed_durability allows you to commit earlier, so, your data may not actually be hardened off onto the storage medium. Thus, your application thinks it’s committed when in reality the data may not have been. This only applies in an error situation where the database goes offline for instance the server crashes.

SQL for the tests:

 

 
use master
go
alter database [delayed_durability_demo] set offline with rollback immediate
alter database [delayed_durability_demo] set online
go
 
drop database [delayed_durability_demo]
go
 
CREATE DATABASE [delayed_durability_demo]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'delayed_durability_demo', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CTP2\MSSQL\DATA\delayed_durability_demo.mdf' , SIZE = 251072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'delayed_durability_demo_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CTP2\MSSQL\DATA\delayed_durability_demo_log.ldf' , SIZE = 2GB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
go
 
alter database delayed_durability_demo set recovery full -- does not affect the demo
backup database delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
go
 
use delayed_durability_demo
go
 
select delayed_durability, delayed_durability_desc, 
       is_memory_optimized_elevate_to_snapshot_on
from sys.databases
where name = db_name()
go
 
--
--- #1
--
create table normal_sql_table (
    id int not null identity primary key clustered,
    padrow char(100) not null,
    inserted datetime not null default( getdate() )
);
 
if @@TRANCOUNT > 0
    rollback tran
 
backup log delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
 
set nocount on;
 
declare @st datetime = getdate()
 
while datediff( second, @st, getdate() ) <= 60
begin
    begin tran
 
    insert normal_sql_table ( padrow )
        values( 'abc' );
 
    commit tran
 
end
go
 
select count(*) as rows_inserted from normal_sql_table
declare @st datetime = ( select min( inserted ) from normal_sql_table )
select datediff( second, @st
                        , inserted ) as run_dur_sec,
        count(*) as rps
from normal_sql_table
group by datediff( second, @st
                        , inserted )
order by run_dur_sec
 
drop table normal_sql_table
go
 
backup log delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
go
 
--
--- #2
--
create table normal_sql_table (
    id int not null identity primary key clustered,
    padrow char(100) not null,
    inserted datetime not null default( getdate() )
);
 
if @@TRANCOUNT > 0
    rollback tran
 
backup log delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
 
set nocount on;
 
declare @st datetime = getdate()
 
begin tran
 
while datediff( second, @st, getdate() ) <= 60
begin
    insert normal_sql_table ( padrow )
    values( 'abc' );
 
end
 
commit tran
go
 
select count(*) as rows_inserted from normal_sql_table
declare @st datetime = ( select min( inserted ) from normal_sql_table )
select datediff( second, @st
                , inserted ) as run_dur_sec,
       count(*) as rps
from normal_sql_table
group by datediff( second, @st
                        , inserted )
order by run_dur_sec
 
drop table normal_sql_table
go
 
backup log delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
go
 
--- #3
--
create table normal_sql_table (
    id int not null identity primary key clustered,
    padrow char(100) not null,
    inserted datetime not null default( getdate() )
);
 
if @@TRANCOUNT > 0
    rollback tran
 
backup log delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
 
set nocount on;
 
declare @st datetime = getdate()
 
while datediff( second, @st, getdate() ) <= 60
begin
    begin tran
 
    insert normal_sql_table ( padrow )
        values( 'abc' );
 
    commit tran with ( delayed_durability = on )
 
end
go
 
select count(*) as rows_inserted from normal_sql_table
declare @st datetime = ( select min( inserted ) from normal_sql_table )
select datediff( second, @st
                        , inserted ) as run_dur_sec,
        count(*) as rps
from normal_sql_table
group by datediff( second, @st
                        , inserted )
order by run_dur_sec
 
drop table normal_sql_table
go
 
backup log delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
go
 
--
--- #4
--
 
alter database delayed_durability_demo set delayed_durability = forced
 
create table normal_sql_table (
    id int not null identity primary key clustered,
    padrow char(100) not null,
    inserted datetime not null default( getdate() )
);
 
if @@TRANCOUNT > 0
    rollback tran
 
backup log delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
 
set nocount on;
 
declare @st datetime = getdate()
 
while datediff( second, @st, getdate() ) <= 60
begin
    begin tran
 
    insert normal_sql_table ( padrow )
        values( 'abc' );
 
    commit tran
 
end
go
 
select count(*) as rows_inserted from normal_sql_table
declare @st datetime = ( select min( inserted ) from normal_sql_table )
select datediff( second, @st
                , inserted ) as run_dur_sec,
        count(*) as rps
from normal_sql_table
group by datediff( second, @st
                    , inserted )
order by run_dur_sec
drop table normal_sql_table
go
 
backup log delayed_durability_demo to disk = 'c:\temp\delayed_durability_demo.bak' with init
go