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
GO

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:

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)