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!