SQL Server 2016 – Enhancements with TempDB

SQL Server 2016 is loaded with tons of new enhancements that I can’t get enough of it. I have been playing around with the bits since multiple CTP’s and it is getting difficult to catch up on all the releases coming so fast and the number of enhancements from performance and scalability point of view have been too many to track. Let us see in this blog post Enhancements with TempDB

When I wrote the enhancements to Setup with SQL Server 2016 and it had a tempdb enhancement section, many wrote back stating there are more to it than just adding it on the setup wizard. So I thought to pen down the current availability of enhancements to TempDB with SQL 2016 here.

In multi-core environments users see Tempdb contention under load and it is one of the common errors one will see. In SQL Server 2016, Microsoft delivered improvements to make tempdb more scalable out of the box. Trust me, I like the thought gone behind it. To my knowledge, these are the key improvements to be anticipated:

  • Users will automatically get multiple tempdb files per instance (if the server is running on more than 1 core) up to a maximum of 8 tempdb files (on machines running on more than 8 or more cores).
  • Users will no longer have to use T-1117 (autogrows all files) or T-1118 (always allocate uniform extents) for tempdb. They are enabled by default on tempdb.
  • Reduce metadata contention on system table pages. Though this is subtle, some of the recommendations/steps DBA’s and Developers used to take to avoid this in prior versions of SQL Server are:
    • Converted temp tables to table variables
    • Avoid using constraints on temp tables
    • Removed TVF’s from queries

One of the readers wrote to me with a link to the T1117 enhancement and asked how they can verify.  You can verify the usage of multiple files and growth using the following query against sys.dm_db_database_page_allocations:

SELECT allocated_page_file_id, COUNT(*) AS Counts
FROM sys.dm_db_database_page_allocations (2, NULL, NULL, NULL, 'LIMITED')
GROUP BY allocated_page_file_id

I have 4 core machine with Hyperthreading enabled, it shows up as 8 cores on my HyperV environment. My files on a default setup looks like:

SQL Server 2016 - Enhancements with TempDB tempdb-sql16-01

So how many of you have used some of the trace flags mentioned above in your environments? How do you feel this will enhance your operational ability in your prods? Do let me know if I missed any other enhancements that needs to be mentioned. This will act surely as a working blog as I discover more.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Server 2016, SQL TempDB
Previous Post
SQL SERVER 2016 – Comparing Execution Plans
Next Post
SQL SERVER – Monitoring and Troubleshooting SQL Server Got Easy with Diagnostics Tool

Related Posts

4 Comments. Leave new

  • Hi Panel, In line with this topic, please can you work me through the best practices on how to shrink and resize this multiple tempdb. For example , my initial size is 8000mb but the whole file have grown to 36000mb and now my drive is out of space. the free space is 99% and i just want to reclaim my space by resizing to it original size. Will the following be a good option?( Note this is in production)

    USE [tempdb]
    DBCC SHRINKFILE (N’tempdev’ , 8000)
    DBCC SHRINKFILE (N’tempdev2′ , 8000)
    DBCC SHRINKFILE (N’tempdev3′ , 8000)
    DBCC SHRINKFILE (N’tempdev4′ , 8000)
    DBCC SHRINKFILE (N’tempdev5′ , 8000)
    DBCC SHRINKFILE (N’tempdev6′ , 8000)
    DBCC SHRINKFILE (N’tempdev7′ , 8000)
    DBCC SHRINKFILE (N’tempdev8′ , 8000)

  • Hi Pinal. Thank you for the useful article! What if I can separate those 8 temp db files on two different drives: 4 on one drive and 4 others on another drive. Will it be efficient or we should put all TEMP DB files on the same drive?


Leave a ReplyCancel reply

Exit mobile version