With the upcoming release of SQL Server 2016, one thing you need to understand is the changes to the way TempDB works. One goal of this new release is “Out of the box” optimization so an administrator should not have to engage in TempDB tuning exercises or tweaking efforts.
One of these important 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.
- -T1118 – When doing allocations for user tables always allocate full extents. Reducing contention of mixed extent allocations
This means that in SQL Server 2016 you will no longer be required to turn on trace flags 1117 or 1118 to support TempDB performance tuning.