SQL Server 2016 is a powerful database management system that comes with a plethora of new enhancements. These enhancements have greatly improved the performance and scalability of SQL Server, making it a popular choice among developers and database administrators.

One of the key areas that has seen significant improvements in SQL Server 2016 is TempDB. TempDB is a system database in SQL Server that is used to store temporary objects such as temporary tables, table variables, and cursors. In multi-core environments, TempDB contention under load is a common issue that can impact the overall performance of the database.

In SQL Server 2016, Microsoft has introduced several enhancements to make TempDB more scalable out of the box. These enhancements aim to reduce contention and improve the overall performance of TempDB.

One of the major enhancements is the automatic creation of multiple TempDB files per instance. If the server is running on more than one core, SQL Server will automatically create multiple TempDB files, up to a maximum of 8 files on machines with 8 or more cores. This allows for better parallelism and reduces contention on TempDB.

Another improvement is the enabling of trace flags T-1117 and T-1118 by default on TempDB. These trace flags were previously used to manage TempDB growth and allocation, but now they are enabled by default. This simplifies the configuration process and ensures optimal performance without the need for manual intervention.

Furthermore, SQL Server 2016 reduces metadata contention on system table pages. This subtle improvement eliminates the need for certain workarounds that DBAs and developers used to implement in prior versions of SQL Server. For example, converting temp tables to table variables, avoiding constraints on temp tables, and removing TVFs from queries.

To verify the usage of multiple TempDB files and growth, you can use the following query against the sys.dm_db_database_page_allocations view:

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

By running this query, you can see the allocation of pages across different TempDB files. This can help you monitor and optimize the performance of TempDB in your environment.

Overall, these enhancements to TempDB in SQL Server 2016 provide significant benefits for users. The automatic creation of multiple TempDB files, the enabling of trace flags by default, and the reduction of metadata contention all contribute to improved scalability and performance.

If you are using SQL Server 2016 or planning to upgrade, make sure to take advantage of these enhancements to optimize the performance of your database. Stay tuned for more updates and discoveries as we continue to explore the capabilities of SQL Server 2016.