One more silent but very useful feature of SQL 2014 is delayed durability.
In memory tables has also durability option but we are talking about normal tables and will see how to use it.
First we have to set database option delayed_durability.
It has 3 options
DISABLED/FORCED/ALLOWED
ALTER DATABASE TEST
SET DELAYED_DURABILITY =DISABLED/FORCED/ALLOWEDIn general this option is disabled
DISABLED :- Delayed durability is disabled and all transactions are fully durable.
ALLOWED:- Each transactions durability is determined at transaction level
by setting option Delayed_Durability = ON/OFF
FORCED:-Every transaction is delayed durable.
To enable from SSMS
Lets see the example to understand it better
CREATE TABLE T
(ID INT,
DATA VARCHAR(100))
Now we will insert data into table
First we will insert data by setting disabled.
DECLARE @DATE1 DATETIME
DECLARE @DATE2 DATETIME
ALTER DATABASE TEST
SET DELAYED_DURABILITY =DISABLED
TRUNCATE TABLE T
SELECT * INTO #before_disabled FROM
sys.dm_io_virtual_file_stats(DB_ID('Test'), NULL);
SELECT @DATE1 = GETDATE()
SET NOCOUNT ON
DECLARE @I INT =1
WHILE @I <= 100000
BEGIN
INSERT INTO T VALUES (@I, 'DATA'+ CONVERT(VARCHAR(10),@I))
SET @I = @I+1
END
SET NOCOUNT OFF
SELECT * INTO #after_disabled FROM
sys.dm_io_virtual_file_stats(DB_ID('Test'), NULL);
SELECT @DATE2 = GETDATE()
SELECT DATEDIFF (SS, @DATE1,@DATE2)
SELECT
B.DATABASE_ID,
B.[FILE_ID],
CASE WHEN A.FILE_ID = 1 THEN 'LOG' ELSE 'DATA' END AS FILE_TYPE,
A.NUM_OF_READS -
B.NUM_OF_READS AS
NUM_OF_READS,
A.NUM_OF_BYTES_READ - B.NUM_OF_BYTES_READ
AS NUM_OF_BYTES_READ,
A.NUM_OF_WRITES -
B.NUM_OF_WRITES AS
NUM_OF_WRITES,
(A.NUM_OF_BYTES_WRITTEN - B.NUM_OF_BYTES_WRITTEN)/(1024*1024) AS
NUM_OF_BYTES_WRITTEN_MB
FROM
#BEFORE_DISABLED AS B
INNER JOIN
#AFTER_DISABLED A ON B.DATABASE_ID
= A.DATABASE_ID
AND B.[FILE_ID]
= A.[FILE_ID]
GO
DECLARE @DATE1 DATETIME
DECLARE @DATE2 DATETIME
SELECT @DATE1 = GETDATE()
ALTER DATABASE TEST
SET DELAYED_DURABILITY =FORCED
TRUNCATE TABLE T
SELECT * INTO #before_enabled FROM
sys.dm_io_virtual_file_stats(DB_ID('Test'), NULL);
SET NOCOUNT ON
DECLARE @I INT =1
WHILE @I <= 100000
BEGIN
INSERT INTO T VALUES (@I, 'DATA'+ CONVERT(VARCHAR(10),@I))
SET @I = @I+1
END
SET NOCOUNT OFF
SELECT * INTO #after_enabled FROM
sys.dm_io_virtual_file_stats(DB_ID('Test'), NULL);
SELECT @DATE2 = GETDATE()
SELECT DATEDIFF (SS, @DATE1,@DATE2)
SELECT
B.DATABASE_ID,
B.[FILE_ID],
CASE WHEN A.FILE_ID = 1 THEN 'LOG' ELSE 'DATA' END AS FILE_TYPE,
A.NUM_OF_READS -
B.NUM_OF_READS AS
NUM_OF_READS,
A.NUM_OF_BYTES_READ - B.NUM_OF_BYTES_READ
AS NUM_OF_BYTES_READ,
A.NUM_OF_WRITES -
B.NUM_OF_WRITES AS
NUM_OF_WRITES,
(A.NUM_OF_BYTES_WRITTEN - B.NUM_OF_BYTES_WRITTEN)/(1024*1024) AS
NUM_OF_BYTES_WRITTEN_MB
FROM
#BEFORE_ENABLED AS B
INNER JOIN #AFTER_ENABLED A ON
B.DATABASE_ID =
A.DATABASE_ID AND
B.[FILE_ID] = A.[FILE_ID]
Here we can see when we are using delayed_durability = on the insert query completes within 4 seconds compare to 22 seconds with delayed_druability = off.
First query takes 100008 writes and 59 MB data was written
After enabling delayed durability it took only 565 writes and 32 MB data was written.
AS we can see the performance is amazing and it can be idea to solve many io related issue where log file and locking is most contending,
Also lets see how much IO was required for each transaction .
Its because its less likely to contend for log IO. Data committed from buffer in large amount reduces contention much more than writing
individual entry for log.
Here risk is that if system crashes due to any reason before data is written from buffer to disk, we will loss data.
Important points to remember.
1. Transactions are asynchronous.
2. Transactions becomes durable when log entries flushed to Disk.
3. It writes transaction log in batches instead individual writes for each operation .
4. Reducing waits in the system
5. Reduces I/O requirements
6. Log is written to disk when buffer fills or durable transaction commits in same DB or running system proc SP_FLUSH_LOG.
