THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

Delayed Durability–I start to like it!

In my previous post about the subject I’ve complained that according to BOL, this feature is enabled for Hekaton only. Panagiotis Antonopoulos from Microsoft commented that actually BOL is wrong – delayed durability can be used with all sorts of transactions, not just In-Memory ones. There is a database-level setting for delayed durability: default value is “Disabled”, other two options are “Allowed” and “Forced”. We’ll switch between “Disabled” and “Forced” and measure IO generated by a simple workload.


Adding table (and re-creating it before every execution):

   2:     DROP TABLE dbo.DDTest;
   4: CREATE TABLE dbo.DDTest
   5: ( 
   6:     id INT NOT NULL IDENTITY(1,1), 
   7:     col1 INT NOT NULL DEFAULT 100,
   8:     col2 VARCHAR(100) NOT NULL DEFAULT 'abcd'
   9: )
  10: GO

We would like to check impact of delayed durability setting on reads and writes to data and log files. So we’ll take snapshot before and after the test and compare. The test itself is very simple – loop that inserts single row with default values into the table – 10 thousand times. Expected results are: at least 10 thousand small log writes when delayed durability is disabled and… hopefully something interesting when it is set to “Forced”.

   1: SELECT * INTO #Before FROM sys.dm_io_virtual_file_stats(DB_ID('DDTest'), NULL);
   2: GO
   4: BEGIN
   5:     SET NOCOUNT ON;
   7:     DECLARE @i INT =1;
   9:     WHILE @i <= 10000
  10:     BEGIN
  11:         INSERT INTO dbo.DDTest( col1, col2 )
  14:         SET @i += 1;
  15:     END;
  16: END;
  17: GO
  19: SELECT * INTO #After FROM sys.dm_io_virtual_file_stats(DB_ID('DDTest'), NULL);
  20: GO

Following query calculates IO between snapshots:

   1: WITH Diff AS
   2: (
   3:         SELECT
   4:             b.database_id, b.[file_id],
   5:             a.num_of_reads - b.num_of_reads as num_of_reads,
   6:             a.num_of_bytes_read - b.num_of_bytes_read as num_of_bytes_read,
   7:             a.num_of_writes - b.num_of_writes as num_of_writes,
   8:             a.num_of_bytes_written - b.num_of_bytes_written as num_of_bytes_written
   9:         FROM
  10:             #Before as b
  11:             INNER JOIN #After a ON b.database_id = a.database_id AND b.[file_id] = a.[file_id]
  12: ),
  13: DBIO as
  14: (
  15:         SELECT
  16:             DB_NAME(d.database_id) as DBName,
  17:             CASE WHEN mf.[type] = 1 THEN 'log' ELSE 'data' END as file_type,
  18:             mf.[name] as [file_name],
  19:             SUM(d.num_of_bytes_written) as [io_bytes_written],
  20:             SUM(d.num_of_writes) as [writes],
  21:             SUM(d.num_of_reads) as [reads],
  22:             SUM(d.num_of_writes + d.num_of_reads) as [io]
  23:         FROM
  24:             Diff d
  25:             INNER JOIN sys.master_files mf ON d.database_id = mf.database_id AND d.[file_id] = mf.[file_id]
  26:         GROUP BY
  27:             DB_NAME(d.database_id),      
  28:             CASE WHEN mf.[type] = 1 THEN 'log' ELSE 'data' END,
  29:             mf.[name]
  30: )
  31: SELECT
  32:         DBName, file_type, [file_name], [io], [reads], [writes],
  33:         CAST(1.0 * [io_bytes_written]/(1024*1024) as DECIMAL(12, 2)) as io_mb_written,
  34:         CAST(1.0 * [io_bytes_written]/[writes] as DECIMAL(12, 2)) as io_bytes_per_write
  36: ORDER BY file_type;

Results vary from test to test but just a few bytes give or take. Generally they are very consistent. And positively surprising:

Durability File Type IO Reads Writes MB Written Avg Bytes per Write
Normal data 1082 0 1082 8.66 8396.42
Normal log 10694 0 10694 42.41 4158.43
Delayed data 107 0 107 1.08 10565.38
Delayed log 124 0 124 3.41 28870.19

Tried to force checkpoint and measure IO after that – no change, seems like black magic. I expected to see less IO with more bytes per write but generally same volume of writes both to data file and log. But results clearly show that we write less – both to data file and to log. And ~10K average IO size in data file drives me crazy – shouldn’t it be 8K???

Also very important observation although expected one, especially on my laptop with its old hard disk: transaction with delayed durability is _much_ faster. 10K writes, even sequential ones, can make huge difference.

Results are slightly different when switching between Simple and Full recovery models but the trend stays. I guess, it can have something to do with minimal logging. Would be interesting to see whether modifying recovery interval affects delayed durability behavior. And also 2 more tests: stress (many sessions doing insert concurrently) and workload with different IO pattern: would it be that effective on bulk loading, for example. And for sure digging in transaction log using fn_dblog should bring something interesting for internals’ geeks among us.

Published Tuesday, November 5, 2013 5:51 PM by Michael Zilberstein



Ian Yates said:

Interesting find - thanks for the blog post.  I wasn't aware of this new feature in SQL 2014, but as an ISV I tend to have to stick with older SQL Server feature sets anyway (I'm advising all clients we no longer support SQL 2005 for example - finally!).

It's nice to see in the BOL article that ACI - sans "D" - is still preserved.  This doesn't change transaction isolation at all as it's just purely buffering the flushing of the log file.  When you think about it it's then clear why the flow on effects, such as log shipping not including those transactions that aren't yet durable, are present.

One potential pitfall is if you set your DB to ALLOW delayed durability you could see some weird wait times.  My understanding of the BOL article is that you could do several delayed durability transactions and then someone comes along and does a transaction that's NOT delayed.  That final transaction has to wait until the delayed transactions are made durable and then the non-delayed one gets its turn.  Again that makes sense since the log is a sequential file but it's still something not immediately apparent when you first read the marketing stuff :)

Finally... BOL didn't mention if this was SQL Enterprise only or if it might be available in Standard or even Express.  Do you know which editions will support this?


November 6, 2013 11:04 PM
New Comments to this post are disabled
Privacy Statement