SQL Server 2014: TEMPDB Hidden Performance Gem

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 (18)

  1. Andy Yun says:

    Hi Bob – thank you for sharing that insight, it's really awesome!  

    Question – does this also affect temporary tables that are created with an explicit CREATE TABLE, inside of a stored procedure?  Or only SELECT INTO #x?

    Thank you!

    Andy Yun

  2. Jeff Moden says:

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

  3. Kevin Boles says:

    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).

  4. Ed Vassie says:

    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?

  5. Montrial Harrell says:

    Thanks for sharing. This is a rellay nice change.

  6. Neeraj Mittal says:

    Thanks Bob, Great insights on TempDB performance enhancement in 2014 !!

  7. Tibco Online Training says:

    It is so nice article. I was really satisfied by seeing this article and we are

    also giving Tibco Online Training. Tibco <a href="http://www.tibco-online-training.com">Tibco online training</a>is best online training institute in USA.

  8. RDORR says:

    Couple of follow-ups

    1.  It may not apply to create table as that may not get bulk activities

    2.  I am looking at a repro for create index that seems to be avoiding the logic – still testing

    3. All SKUs do get this

    4. Delayed Durability is completely separate from this fix

  9. Kendra Little says:

    Hi Bob,

    Does this KB mean that the fix has been back ported into SQL Server 2012 SP1 CU 10?


    (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

  10. Rei (sqlmii) says:

    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?

  11. RDORR says:

    This issue has been addressed in SQL Server 2012 Hotfix


    • Cumulative Update 1 for SQL Server 2012 SP2

    • Cumulative Update 10 for SQL Server 2012 SP1

  12. Andrew K says:

    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

  13. Fredrik Elmqvist says:

    Nice little gem in this performane increase! Was this ported into SQL2012?

  14. Praveen says:

    > 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

    1. Мирон says:

      I believe it’s not “SQL Server” pages. It’s memory pages.

      Here is some info https://en.wikipedia.org/wiki/Page_%28computer_memory%29#Page_size_trade-off

  15. Jay Sanati says:

    It’s a shame that there is not even one answer for asked questions 🙁

Skip to main content