Whats new with TempDB in SQL Server 2016

Its always quite interesting topic TempDB in SQL Server. Microsoft SQL Server core engine team continuously working on improving the performance versions to version and its one of the area TempDB that they focused a lots recently. If you need to get an overview of TempDB and its importance, I highly recommend to read earlier post.

Today, we are going to see some of the enhancements of TempDB in SQL Server 2016. If you wonder of its rationale of such late post, let me tell you I had to face a question int his area and thought its good to have a post to share with everyone if that helps to a larger people.

While we migrated our database server from SQL 2008 R2 to SQL 2016, we had made quite few changes at the configuration level. One of the changes is we completely removed the Trace Flags (TF -1117 and 1118) from start up parameters. So, today there was q question from one my co-worker why did we remove this TFs in SQL Server 2016? So, let us see the reason here, perhaps a little more than this can cover the enhancements in SQL Server 2016 for Tempdb database.

Few changes in SQL Server 2016

    1. SQL server 2016 decides on the number of TempDB files as default to use by considering the number of logical processors on the server. This helps to avoid the latch contention issues with a single TempDB file (by default in earlier version). Ofcourse, this configuration can be easily changed by experienced database administrators.

    2. Inclusion of Trace flags 1117 and 1118 in the database engine and no longer needed to activate.

    Excerpt from MSDN documentation on TF 1117 & 1118 as below.

    3. Temporary table object caching

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

One thought on “Whats new with TempDB in SQL Server 2016”

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