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;
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:
create table myTempDbTable
id int identity(1, 1) not null
insert into myTempDbTable
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
add event sqlserver.log_flush_requested
database_id = 2 -- tempdb
add target package0.event_file
filename = N'\\<Server>\<Share>\<Folder>\LogFlushRequested.xel'
alter event session LogFlushRequested
state = start;
state = stop;
drop event session LogFlushRequested
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.
So delayed durability effectively is always on for tempdb and has always been.