SQL Server 2014: Tempdb hidden performance enhancement feature




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.  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 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 without going to disk.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s