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:
<RelOp EstimateRows="2540" EstimateRowsWithoutRowGoal="2540">
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!
ORDER BY (SELECT NULL) 
Hello Joe.
Do you plan to expand this list in the future? If so, it may be worth to mention the article New Undocumented Trace Flags?
Also could you please disclose your methodology for finding new trace flags?
Good point. I’m not sure yet. I mainly put this up as a reference for your list. I do plan on publishing the methodology at some point.
Hello, Joe.
Here is an explantion for some of the TFs you have published from my undocumented TFs repository based on my own experience.
2418 – Disable Serial Batch Mode processing.
8665 – Disable Local/Global aggregation.
8688 – Disable parallel Scan
8750 – Skip search0 optimization phase and move to search1
8799 – Force unordered Scan
9164 – Disables HM join (as well as 8743 – disable SM join, 9165 – disable NL join)
10809 – Force Stream Aggregate for scalar aggregation in batch mode
Hope that helps!
Dima
Hi Dima,
Thank you so much for the corrections and the trace flag to disable merge joins. That one could be useful for some of the testing that we do. I’m a big fan of your work!
Thanks again,
Joe