Below is a list of trace flags which, as far as I can tell, have never been publicly documented. I did not fully investigate many of them and many of the descriptions are just guesses. I make no guarantees and none of these should be used in production. All tests were performed on SQL Server 2017 CU2 with trace flags enabled at the global level. Special thanks to Dmitry Pilugin for offering a few corrections.
Trace Flag List
166 – Unclear. Observable effect was to change the identifier for act1008 to act1009 in a query plan.
304 – Changed the reported CachedPlanSize.
861 – According to the error log this disables buffer pool extension.
862 – According to the error log this enables buffer pool extension. This TF probably doesn’t do anything anymore.
2368 – For one query, this resulted in a parallel plan significantly more expensive than the naturally occurring serial plan. Could be related to trace flag 3651.
2374 – Removes QueryHash and QueryPlanHash information from estimated query plans.
2387 – There was a small change in CPU and IO costs for some operators. Full effect unknown.
2399 – Small changes in operator costs were observed for some queries. These were typically less than 0.01 units.
2418 – According to Dima, this trace flag disables serial Batch mode processing.
3651 – Can cause stack dumps. For one query, this resulted in a parallel plan significantly more expensive than the naturally occurring serial plan.
7356 – Added a probe residual to an adaptive join. Full effect unknown.
7398 – Changed a nested loop join to have ordered prefetch.
8665 – According to Dima, this trace flag disables local/global aggregation.
8678 – For one query this changed a bushy plan to a left deep one. There was no change in cost. Full effect unknown.
8688 – According to Dima, this trace flag disables parallel scans.
8741 – Resulted in a different join order for some queries with a higher estimated cost. Perhaps this disables Transitive Predicates? Full effect unknown.
8742 – Resulted in a different join order for some queries. Full effect unknown.
8750 – According to Dima, this trace flag skips search 0 optimization phase and moves to search 1.
8799 – According to Dima, this trace flag forces unordered scans.
9114 – Implemented a (SELECT 1) = 1 predicate as a join instead of optimizing it away.
9164 – According to Dima, this trace flag disables hash joins.
9165 – Removed an index recommendation from a plan.
9182 – Resulted in a very strange cost change to a clustered index delete.
9183 – Same observed effect as trace flag 9182.
9236 – Resulted in a different join order for some queries. Full effect unknown.
9251 – Change in cardinality estimates for some queries. It might only work with the legacy CE. Full effect unknown.
9260 – Adds an explicit sort before creation of an index spool. Almost doesn’t change the total estimated cost. Might be identical plans with just more detail shown at that step.
9284 – Changed the order of a scalar operator comparison in a single join for certain queries. Full effect unknown.
9287 – Appears to disable partial aggreation.
9341 – Resulted in a rather odd plan for a COUNT(DISTINCT) query against a CCI.
9346 – Appears to disable batch mode window aggregates.
9384 – Very slightly changed the memory grant of a query with a batch mode window aggregate.
9390 – Resulted in plan changes including parallelism for queries that shouldn’t have been eligible for parallelism based on CTFP. Full effect unknown.
9412 – Removes the new OptimizerStatsUsage information from estimated query plans.
9447 – Forces query plans to use the new referential integrity operator when validating UPDATE and DELETE queries against foreign key parent tables.
9473 – Change in cardinality estimates for some queries. Full effect unknown.
9474 – Change in cardinality estimates for some joins in certain queries. Full effect unknown.
9477 – Slight change in ratio of EstimateRebinds and EstimateRewinds was observed. Full effect unknown.
9478 – Change in cardinality estimates for some joins in certain queries. Full effect unknown.
9480 – Reduced the selectivity of a bitmap filter from 0.001 to 0.000001. Full effect unknown.
9484 – Slight change in estimated number of rewinds. Full effect unknown.
9490 – Change in cardinality estimate. Full effect unknown.
10809 – According to Dima, this trace flag force stream Aggregates for scalar aggregation in batch mode.
11001 – Results in a different join order for some queries. Full effect unknown.
11029 – Prevents new information about row goals from getting logged to the plan cache. Example of what you get without it in 2017 CU2:
Final Thoughts
Perhaps one day I will come back to some of these to investigate them further. Going through this exercise gave me a new appreciation for those among us who can state the behavior of undocumented trace flags with confidence. Thanks for reading!