Trace Flags Info

• TF 1118  – This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion. As Paul Randal says, “Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on”. In fact, for SQL Server 2016, Microsoft has now included the behavior from TF 1118 in the product by default, so you don’t need to enable it for SQL Server 2016.

• TF 2371 – This trace flag changes the threshold for automatic statistics updates from the old default of needing 20% of the rows in a table to have been updated to trigger a statistics update to a dynamic % that decreases as the table row count increases. This means that you will get more frequent automatic statistics updates on larger tables. Even with this change, it is still a good idea to use SQL Server Agent jobs to periodically trigger manual statistics updates on your most volatile tables. Personally, I think there is no downside to having this enabled. Someone at Microsoft must agree, since for SQL Server 2016, they have now included the behavior from TF 2371 in the product by default, so you don’t need to enable it for SQL Server 2016.

• TF 3226 – This trace flag prevents SQL Server from writing information to the SQL Server Error log after every successful database backup (which includes Full, Differential and Log backups). It will still log failed log backups, which is what you really care about anyway. Records of all database backups are still stored in the msdb system database after enabling this trace flag. Personally, I think there is no downside to having this enabled, and it makes it much easier to find more relevant information in the SQL Server Error log when this trace flag is enabled.

http://www.sqlservice.se/updated-microsoft-sql-server-trace-flag-list/