SQL 2016 – It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases

Configuration of TEMPDB is often critical to scalability and throughput of SQL Server applications.The following link (https://support.microsoft.com/en-us/kb/2964518) outlines how to configure SQL Server 2014 and 2012 for optimal scalability and performance.

A SQL Server 2016 primary goal was 'It Just Works.'Out of the box a customer should not have to engage in such documentation and tuning exercises.Whenever possible SQL Server should just work and provide the intended scalability and performance outcomes.

TEMPDB

One of these changes is TEMPDB always assumes -T1117 and -T1118 behavior.

-T1117 - When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention points.

In summary, SQL Server 2016 no longer requires one to turn on TF 1117 or 1118.

User Database

For User Databases, trace flags 1117 and 1118 have been replaced with new extensions in ALTER DATABASE commands.Use the ALTER DATABASE syntax to enable or disable the desired trace flag behavior at a database level.

-- Trace Flag 1118

Trace flag 1118 for user databases is replaced by a new ALTER DATABASE setting - MIXED_PAGE_ALLOCATION.

Default value of the MIXED_PAGE_ALLOCATION is OFF meaning allocations in the database will use uniform extents.

The setting is opposite in behavior of the trace flag (i.e. TF 1118 OFF and MIXED_PAGE_ALLOCATION ON provide the same behavior and vice-versa).