Share via

SQL Server 2014: TEMPDB Hidden Performance Gem

  • Article

I ran across a change for TEMPDB BULK OPERATIONS (Select into, table valued parameters (TVP), create index with SORT IN TEMPDB, …) that you will benefit from.

For example, I have a Create Index … WITH SORT IN TEMPDB that takes ~1 minute in SQL Server 2012. On the same machine using a SQL Server 2014 instance, the index builds in 19 seconds.

SQL Server has had a concept of eager writes for many versions.  The idea is to prevent flooding the buffer pool with pages that are newly created, from bulk activities, and need to be written to disk (write activities.)  Eager writes help reduce the pressure on lazy writer and checkpoint as well as widening the I/O activity window, allowing for better performance and parallel usage of the hardware.

The design is such that bulk operations may track the last ## of pages dirtied, in a circular list.   When the list becomes full old entries are removed to make room for new entries.   During the removal process the older pages are put in motion to disk, if still dirty – API: WriteFileGather.    The intent is to gather up to 128K, contiguous dirty pages (32) and write them out.

The change in SQL Server 2014 is to relax the need to flush these pages, as quickly, to the TEMPDB data files.  When doing a select into … #tmp … or create index WITH SORT IN TEMPDB the SQL Server now recognizes this may be a short lived operation.   The pages associated with such an operation may be created, loaded, queried and released in a very small window of time.

For example:   You could have a stored procedure that runs in 8ms.  In that stored procedure you select into … #tmp … then use the #tmp and drop it as the stored procedure completes.

Prior to the SQL Server 2014 change the select into may have written all the pages accumulated to disk.  The SQL Server 2014, eager write behavior, no longer forces these pages to disk as quickly as previous versions.   This behavior allows the pages to be stored in RAM (buffer pool), queried and the table dropped (removed from buffer pool and returned to free list) without ever going to disk as long memory is available.   By avoiding the physical I/O when possible the performance of the TEMPDB, bulk operation is significantly increased and it reduces the impact on the I/O path resources as well.

The pages used in these operations are marked so lazy writer will favor writing them to TEMPDB are returning the memory to the free list before impacting pages from user databases, allowing SQL Server to handle some of your TEMPDB operations with increased performance.

In progress, no promises: We are actively investigating a port of this change to SQL Server 2012 PCU2 so your SQL Server 2012 installations can take advantage of the performance increase as well.

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

  • Anonymous
    April 10, 2014
    The comment has been removed

  • Anonymous
    April 12, 2014
    How ironic.  Up until this very moment, I thought that was the way it always worked.  Thanks for the post.

  • Anonymous
    April 13, 2014
    I am REALLY liking this! Presumably it is in ALL editions of SQL Server, which makes it even more important and useful to a much wider audience than most of the 2014 new and enhanced features (which are Enterprise Edition only).

  • Anonymous
    April 13, 2014
    I am guessing from this note that the Delayed Durability feature is permanently turned on for tempdb access, or are these improvements something separate to Delayed Durability?

  • Anonymous
    April 13, 2014
    Thanks for sharing. This is a rellay nice change.

  • Anonymous
    April 20, 2014
    Thanks Bob, Great insights on TempDB performance enhancement in 2014 !!

  • Anonymous
    April 21, 2014
    It is so nice article. I was really satisfied by seeing this article and we arealso giving Tibco Online Training. Tibco <a href="http://www.tibco-online-training.com">Tibco online training</a>is best online training institute in USA.

  • Anonymous
    April 24, 2014
    Couple of follow-ups It may not apply to create table as that may not get bulk activities  I am looking at a repro for create index that seems to be avoiding the logic - still testing All SKUs do get this Delayed Durability is completely separate from this fix

  • Anonymous
    April 29, 2014
    you're linked and feature is tested here: www.brentozar.com/.../memory-ssd-tempdb-temp-table-sql-2014

  • Anonymous
    May 23, 2014
    Hi Bob,Does this KB mean that the fix has been back ported into SQL Server 2012 SP1 CU 10?support.microsoft.com/.../en-us(I wish I had time to get that installed and test quickly, but it's going to be a while before I can do that, so wanted to ask if you knew.)Thanks so much for this post and for bringing this cool new feature to light! I think it's pretty incredible.Kendra Little

  • Anonymous
    July 31, 2014
    Thank you for sharing it Bob. This looks like a great feature to explore and take advantage of SQL Server 2014. Given SP2 for SQL Server 2012 has been released in June, has it been ported?

  • Anonymous
    December 17, 2014
    This issue has been addressed in SQL Server 2012 Hotfixsupport.microsoft.com/.../2958012Cumulative Update 1 for SQL Server 2012 SP2 Cumulative Update 10 for SQL Server 2012 SP1

  • Anonymous
    December 22, 2014
    Great new feature, is not actually a famous one, only have seen a few places mentioning it, you can also check this list with great info in it, some even cover specifics on how to implement each feature sqlturbo.com/sql-server-2014-top-8-new-features

  • Anonymous
    March 23, 2015
    Nice little gem in this performane increase! Was this ported into SQL2012?

  • Anonymous
    May 19, 2015
    > The intent is to gather up to 128K, contiguous dirty pages (32) and write them out. Is it 16 Pages or 32 Pages? 128/8=16

  • Anonymous
    June 05, 2016
    I don't agree, read that http://www.sqlservercentral.com/blogs/mssqlfun/2014/09/04/how-to-start-sql-server-in-single-user-mode-/ Best regards, Ursula

  • Anonymous
    August 23, 2016
    It's a shame that there is not even one answer for asked questions :(