SQL Server 2016: TempDB enhancement – by default turn on trace flags 1117 and 1118

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

I am exploring SQL Server 2016 and every time I am getting something new and effective enhancements those added in SQL Server 2016.

Whenever you are searching for TempDB optimization tips, you can find one note for trace flags 1117 and 1118 which are for Allocations and Auto Growth.

You can use trace flag 1117 – When growing a data file grow all files at the same time, so they remain the same size, reducing allocation contention points.
You can use trace flag 1118 – When doing allocations for user tables always allocate full extents. It reduces contention of mixed extent allocations.

Eariler, you can turn on these trace flags at server level which impacts to other databases as well.
And now the great enhancement by SQL Server 2016 is, it enables these flags by default for TempDB and customize for other user databases.

In the next post, You can find about How to enable trace flag 1117 and 1118 on user databases.