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