What’s the issue?
Trace flags are switches that change SQL Server’s default behavior, used either to address specific performance issues or to enable behaviors that have proven beneficial for most workloads. Some trace flags address behaviors so commonly problematic that they are widely recommended as default startup flags on the affected SQL Server versions.This finding flags instances that are missing one or more of the following recommended trace flags based on the SQL Server version: 1117, 1118, and 2371 for SQL Server 2014 (version 12) and earlier; 7752 for SQL Server 2016 and 2017 (versions 13 and 14) when Query Store is in use; 7745 for SQL Server 2016 and later when Query Store is in use; and 3226 for all versions.
Each of these flags addresses a specific behavior that, if left at default on the affected versions, can produce measurable performance or operational issues.
Why is this a problem?
Trace flag 1117 forces all data files in a filegroup to grow at the same time when one file hits the autogrowth threshold, keeping files evenly sized so SQL Server’s proportional fill algorithm distributes activity evenly. Without it on SQL Server 2014 and earlier, tempdb data files often become unevenly sized and contention concentrates on the largest file. Starting in SQL Server 2016 this behavior is controlled by the AUTOGROW_ALL_FILES database option, and the trace flag has no effect.Trace flag 1118 forces uniform extent allocation instead of mixed extents, reducing contention on SGAM allocation pages in tempdb. Without it on SQL Server 2014 and earlier, heavy tempdb workloads experience PAGELATCH contention on SGAM pages. Starting in SQL Server 2016 this behavior is controlled by the MIXED_PAGE_ALLOCATION database option (off by default for tempdb), and the trace flag has no effect.
Trace flag 2371 changes the threshold for automatic statistics updates from a fixed 20 percent of rows changed to a dynamic, smaller threshold for large tables. Without it on SQL Server 2014 and earlier, statistics on large tables become stale long before SQL Server updates them, leading to poor query plans. Starting in SQL Server 2016 with database compatibility level 130 or higher, this behavior is the default and the trace flag has no effect.
Trace flag 7752 enables asynchronous load of Query Store data at database startup, so user queries are not blocked while Query Store loads. Without it on SQL Server 2016 and 2017, Query Store databases can experience slow startup behavior. Starting in SQL Server 2019, this behavior is controlled by the engine and the trace flag has no effect.
Trace flag 7745 prevents SQL Server from waiting to flush Query Store data to disk during a database shutdown or failover. Without it on SQL Server 2016 and later, shutdowns and Always On failovers can be delayed waiting for Query Store data to be written, extending downtime. This trace flag remains relevant in current versions.
Trace flag 3226 suppresses successful backup messages from the SQL Server error log. Without it, frequent log backups (often every few minutes) clutter the error log with success messages, making it difficult to find actual errors when troubleshooting. This trace flag is widely recommended on all versions and has no negative side effects, since backup history is still recorded in msdb regardless.
What should you do about this?
Identify the SQL Server major version of each instance using SELECT SERVERPROPERTY(‘ProductMajorVersion’); and determine which trace flags from the list above apply. Check currently active trace flags with DBCC TRACESTATUS(-1); to see what is already enabled.
Add the appropriate trace flags as startup parameters through SQL Server Configuration Manager, under the SQL Server service properties on the Startup Parameters tab. Each trace flag is added with the format -T
Test trace flag changes in a non production environment first, particularly before adding them to instances with unusual workloads or vendor applications that may have specific trace flag requirements of their own. Confirm the trace flag is active after the restart by running DBCC TRACESTATUS(-1); again.
Read more…
Trace Flags (Transact-SQL) – SQL Server | Microsoft Learn