Applying DELAYED_DURABILITY = FORCED on TEMPDB

 

My PFE colleague Sam Mesel posted the following information a few days ago on an internal distribution group:

I’m testing DELAYED_DURABILITY on TempDB

Applying the following change on it does not give me any error message, but I see no performance improvements.

ALTER DATABASE [tempdb] SET DELAYED_DURABILITY = FORCED

Is this the expected behavior for system databases ?

And another PFE colleague, Tom Stringer, responded:

I just did a test in my environment by setting delayed durability as forced for tempdb:

alter database tempdb

set delayed_durability = forced;

go

select

       name,

       delayed_durability_desc

from sys.databases

where name = 'tempdb';

And then I created an XEvents session by capturing the log_flush_requested event. This event has an event column of is_delayed_durability and while running this session I ran a quick query:

use tempdb;

go

create table myTempDbTable

(

       id int identity(1, 1) not null

);

go

begin tran;

insert into myTempDbTable

default values;

commit tran;

Looking at the output of the log_flush_requested event for this duration, I see that is_delayed_durability is false. So with my quick test it looks like forcing delayed durability is not in fact recognized for tempdb. But again this is a quick and isolated test.

create event session LogFlushRequested

on server

add event sqlserver.log_flush_requested

(

where

(

              database_id = 2 -- tempdb

)

)

add target package0.event_file

(

set

filename = N'\\<Server>\<Share>\<Folder>\LogFlushRequested.xel'

);

alter event session LogFlushRequested

on server

state = start;

go

/*

alter event session LogFlushRequested

on server

state = stop;

go

drop event session LogFlushRequested

on server;

go

*/

So I decided to have a look at the source code of the product to see whether SQL Server was intentionally coded to treat TEMPDB differently and whether that behavior was written in the feature specifications or not. Here are my findings:

It’s working as per the functional specs. TempDB doesn’t honor the durability settings or commit semantics. TempDB transactions commit without waiting for the log to harden, regardless of those two. For TempDB, LCs are lazily (and only eventually) hardened. (LC stands for Log Cache, which is an in-memory buffer in which log records can be formatted. Before a log cache is to be written to disk, it is converted into a log block.)

Since this special behavior is not officially and publicly documented in the product, I’ve filed a documentation defect so that the topic on Control Transaction Durability gets improved with such addition in any of the upcoming documentation refresh.

Comments

• Anonymous
  July 22, 2014
  So delayed durability effectively is always on for tempdb and has always been.