Trace Flags That Are Now Defaulted Behaviors In SQL Server

If you have ever attended one of my performance tuning sessions, you know I tend to talk about trace flags. Trace Flags can help fix performance issues and some are now defaulted in later SQL Server versions. In my opinion, when a trace flag’s behavior defaulted in a version, then you should potentially put them in place within environments that do not have them implemented. Below, are a few of these particular traces flag along with Microsoft’s definition of what each trace flag does, taken straight from MS documents. I have also included a brief commentary on each one. As with any change, you should be sure to thoroughly test before implementing these trace flags into any production environment.

Trace Flag 1117

When a file in the filegroup meets the autogrowth threshold, all files in the filegroup grow. This trace flag affects all databases and is recommended only if every database is safe to be grow all files in a filegroup by the same amount. Starting with SQL Server 2016 (13.x) this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE

This forces all data files to grow at the same rate when a growth event is trigger. Keeping all files, the same sizes helps reduce contention and shares the work load evenly across the files. This especially useful for Tempdb.

Trace Flag 1118

Forces page allocations on uniform extents instead of mixed extents, reducing contention on the SGAM page. When a new object is created, by default, the first eight pages are allocated from different extents (mixed extents). Afterwards, when more pages are needed, those are allocated from that same extent (uniform extent). The SGAM page is used to track these mixed extents, so can quickly become a bottleneck when numerous mixed page allocations are occurring. This trace flag allocates all eight pages from the same extent when creating new objects, minimizing the need to scan the SGAM page. SQL Server 2016 (13.x) this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE , and trace flag 1118 has no effect.

This has to with tempdb contention. This forces SQL Server to allocate an entire extent (8 pages) when tables are created, rather than a single page. This can reduce contention within certain TempDB internal structures (GAM and SGAM pages).

Trace Flag 2371

Changes the fixed update statistics threshold to a linear update statistics threshold. SQL Server 2016 (13.x) and under the database compatibility level 130 or above, this behavior is controlled by the engine and trace flag 2371 has no effect.

This changes the auto update statistics algorithm from using a flat rate of 20% + 500 rows being changed to trigger an auto update stats event, to using a sliding scale. This drastically increases the frequency of statistics updates. You can find my blog on this one here.

Trace Flag 2453

Allows a table variable to trigger recompile when enough number of rows are changed. This became table variable deferred compilation in SQL 2019

Previously, table variables had an estimated row count of 1 which causes poor execution performance. This now allows the optimizer to get a better estimate verses actual rows resulting in a better execution plan.

Trace Flag 3427

Enables a fix for the issue when many consecutive transactions insert data into temp tables in SQL Server 2016 (13.x) where this operation consumes more CPU than in SQL Server 2014 (12.x). This trace flag applies to SQL Server 2016 (13.x) SP1 CU2 through SQL Server 2016 (13.x) SP2 CU2. Starting with SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x), this trace flag has no effect.

This simply helps with TEMPDB contention and CPU consumption especially for those workloads with a very large number of inserts and updates.

Trace Flag 4199

Enables Query Optimizer (QO) fixes released in SQL Server Cumulative Updates and Service Packs. QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 being enabled. Starting with SQL Server 2016 (13.x), to accomplish this at the database level, see the QUERY_OPTIMIZER_HOTFIXES option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Starting with SQL Server 2016 (13.x) SP1, to accomplish this at the query level, add the USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES' query hint instead of using this trace flag.

My blog explains more about 4199 and hotfixes here be sure to read it for a full understanding. This one is a little different as it is still optional and not defaulted however they have changed this to a scope configuration so that’s why I am mentioning it.

Trace Flag 6498

Enables more than one large query compilation to gain access to the big gateway when there is sufficient memory available. This trace flag can be used to keep memory usage for the compilation of incoming queries under control, avoiding compilation waits for concurrent large queries. It is based on the 80 percentage of SQL Server Target Memory, and it allows for one large query compilation per 25 gigabytes (GB) of memory. Starting with SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 6498 has no effect.

This on was new to me but has to do with the wait type RESOURCE_SEMAPHONE_QUERY_COMPILE. It’s when a large query needs to access more memory then the threshold it allows it to proceed if the memory is available. You can see what your memory threshold is set at by using the DBCC MemoryStatus command.

Trace Flag 6532

Enables performance improvement of query operations with spatial data types in SQL Server 2012 (11.x) and SQL Server 2014 (12.x). The performance gain will vary, depending on the configuration, the types of queries, and the objects. Starting with SQL Server 2016 (13.x) this behavior is controlled by the engine and trace flag 6532 has no effect.

This is not one I have actually had to use since I rarely address systems with spatial data.

Trace Flag 6533

This is not one I have actually had to use since I rarely address systems with spatial data.

Trace Flag 7412

Enables the lightweight query execution statistics profiling infrastructure. The performance overhead to collect information about execution plans was reduced with the introduction of lightweight profiling. This trace flag applies to SQL Server 2016 (13.x) SP1 and higher builds. Starting with SQL Server 2019 (15.x) this trace flag has no effect because lightweight profiling is enabled by default.

I used this a lot. Unlike standard statistics profiling, lightweight profiling does not collect CPU runtime information. However, lightweight profiling still collects the very useful row count and I/O usage information.

Trace Flag 7752

Enables asynchronous load of Query Store.

Note

Starting with SQL Server 2019 (15.x) this behavior is controlled by the engine and trace flag 7752 has no effect.

Queries that are executed can be held up while QS loads data. This trace flag prevents this from happening. You will need this trace flag for 2016 and 2017 SQL Versions.

Summary

Anytime trace flags are replaced with defaulted behaviors we should be paying attention and fixing these issues in our lower environments. This blog is intended to just bring these to your attention and to encourage you to take the time to look into the ones I have mentioned above. If Microsoft has taken the thought that these should be defaulted behaviors in later versions, then “maybe” you should really consider applying the fixes in the prior versions. A full list of trace flags including these can be found on docs.Microsoft. Be sure to education yourself on these if you are not already familiar and once again ALWAYS test.

NOTE
There are a few additional trace flags, that I haven't use, that have been changed, about NUMA and one about VAS fragmentation with page allocation errors, to defaulted behaviors 8048, 8075, 8079 you can read about at the link above.