Tempdb Trace Flags 1117 and 1118 are no longer needed in SQL Server 2016. 1117 flag is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE and 1118 flag is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE.
What are these Trace Flags
TRACE FLAG 1117 – GROW ALL FILES IN A FILE GROUP EQUALLY
Trace flag (TF) 1117 is related strictly to file groups and how data files grow within them. A file group is a logical container for one or more data files within a database. TF 1117 forces all data files in the same file group to grow at the same rate, which prevents one file from growing more than others, leading to the hotspot issue described earlier in this chapter. Enabling this trace flag in earlier versions of SQL Server is a minor tradeoff in performance. For example, if you were using multiple data files in user databases, this trace flag affects them as well as TempDB’s data files. Depending on your scenario, that could be problematic—an example would be if you had a file group that you did not want to grow as a single unit. Starting with SQL Server 2016, the behavior to grow all data files at the same rate is built into TempDB by default, which means you no longer need this trace flag.
TRACE FLAG 1118 – FULL EXTENTS ONLY
Administrators use trace flag 1118 to change page allocation from a GAM page. When you enable TF 1118, SQL Server allocates eight pages, or one extent, at a time to create a dedicated (or uniform) extent, in contrast to the default behavior to allocate a single page from a mixed extent. Unlike with TF 1117, there was no potential downside to enabling TF 1118—it is generally recommended for all SQL Server implementations in earlier releases. Starting with SQL Server 2016, all allocations of TempDB pages use uniform extent allocation, thus eliminating the need to use TF 1118.
In earlier versions of SQL Server, the default configuration uses one data file for TempDB. This limitation sometimes results in page-latch contention, in order to mitigate/remedy this behavior in SQL Server is to add more data files, in turn SQL Server creates more of three special types of pages (SGAM, GAM and PFS) and gives more throughput to TempDB. Importantly, these files should all be the same size. Creation of multiple data files depends on many factors like number of cores vs CPU sockets, Hyper-threading. Microsoft suggested in the article KB2154845, about the guidelines/recommendation to reduce allocation contention.
The recommendation in the KB2154845 is now built into product setup in SQL Server 2016. When you install SQL Server 2016, the default configuration of TempDB now adjusts to your environment, as show in the below Figure.
Hope you enjoyed the post!
Cheers
Ramasankar Molleti
LinkedIn: LinkedIn Profile
Twitter: Twitter
Ramasankar Molleti Blog 