Compilation Behavioral

All flags in this category modify the compilation process in such a way as to produce alternative query plans. Note that many flags in the Statistics and Estimation category also modify compilation behavior; those flags all do so by modifying the cardinality estimation process, whereas all of these flags change non-estimation behaviors.

The information returned falls into the below categories:

  1. Disk IO Optimization –> Flags whose goal is to improve Disk IO performance (often by promoting sequential IO)
  2. Hashing and Batching –> Flags that modify hashing operations and flags that adjust batch mode processing
  3. Phases and Timeouts –> Flags that alter the phases the optimizer enters or its timeout values for short-circuiting phase entry.
  4. Miscellaneous –> Other, miscellaneous flags.
  5. Fixes and Past Relevance –> Fix flags and flags which are no longer relevant.

Note that the line between these flags and those in the Query Execution category can be a bit grey. Flags in this category affect behaviors that persist in the cached plan between executions, while flags in the Query Execution category affect behavior that is normally decided at runtime.

Short Descriptions

Flag Short Description
See Also  
2301 Modifies estimation mathematical assumptions; may affect non-estimation behavior.
* 2312 Forces the use of the new CE during optimization, inevitably changing plan shapes.
Disk Opts  
2332 Forces a sort (for sequential IO) in Insert/Update/Delete plans
2340 Disables nested loops “implicit batch sort” (in post-opt rewrite phase)
8633 Enable prefetch; useful for IO perf in Insert/Update/Delete plans.
8738 (Apparently) disables sorting before a key lookup operator.
8744 Disables pre-fetching for the Nested Loops operator.
8795 Disables sorting for sequential IO in Insert/Update/Delete plans.
9115 Disables both implicit batch sort (TF 2340) and NL prefetching (TF 8744)
2441 Enables the use of hash joins to column store indexes in certain cases.
7359 Disables the internal bitmap used for hash matches and joins.
7497 (Full purpose unknown) Can be used w/7498 to disable “optimized bitmaps”
7498 (Full purpose unknown) Can be used w/7497 to disable “optimized bitmaps”
9347 (Purpose unknown) Appears related to batch mode sorting.
9349 Disables batch mode top sort operator.
9358 Disables batch-mode sort operations.
9453 Disables batch mode, forcing row mode.
8671 Disables the optimizer short-circuit due to “Good Enough Plan Found”
8677 Skips the “Search 1” optimization phase (if applicable)
8757 Skip the Trivial Plan optimization phase
8780 Sets the optimizer timeout value to a very high constant. Do Not Use!
8788 Appears to have a similar effect as 8780. Do Not Use!
2329 Disables the “Few outer rows” dimension table optimization
2335 Causes optimizer to create plans that are more conservative w/memory.
7470 Adjusts calculation for memory requirements for sorts.
8602 Tells optimizer to ignore index hints.
* 8649 Strongly encourages optimizer to generate a parallel plan.
8690 Prevents the optimizer use of “performance spools”
8692 Forces optimizer to use eager spools when it needs Halloween Protection.
8722 Disables all “other” (besides index and join) query hints.
8746 Among other effects, disables “rowset sharing” optimization.
8755 Disables all join hints.
8758 Among other effects, disables plan rewrites to a single operator plan.
8790 Forces a wide-update plan for any data-changing query.
9130 Disables the pushing of non-sargable filter predicates into seeks or scans.
9348 Applies a row limit to whether bulk insert is attempted or not.
8687 (Perhaps) disables query parallelism.
8720 In SQL 2000, apparently had the same effect as OPTION(KEEPFIXED PLAN)
9059 Allows optimizer to choose an index seek for numerics of varying precisions.

Disk IO Optimization


PWhite: “Force DML Request Sort (CUpdUtil::FDemandRowsSortedForPerformance)” Appears to be the counterpoint of 8795.


2340 Doc2014

BOL 2014: “Causes SQL Server not to use a sort operation (batch sort) for optimized nested loop joins when generating a plan. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the DISABLE_OPTIMIZED_NESTED_LOOP USE HINT query hint instead of using this trace flag.”

Dima: “Disable Nested Loops Implicit Batch Sort on the Post Optimization Rewrite Phase.” Dima’s article is useful to contrast NL batch sorting and 2340 from NL prefetching and 8744, and discusses 9115.

2009160 | CSS_1 | Dima_1


PWhite: “Enable prefetch (CUpdUtil::FPrefetchAllowedForDML and CPhyOp_StreamUpdate::FDoNotPrefetch)”



(Apparently) disables an optimization where rows are sorted before a Key Lookup operator. The optimization is meant to promote Sequential IO rather than the random nature of IO from Key Lookups. Note that the context in which this flag is described means that the above description may not be very precise, or even the only use of this flag.


8744 Doc2014

BOL 2014: “Disable pre-fetching for the Nested Loop operator.” PWhite: “Disable prefetch (CUpdUtil::FPrefetchAllowedForDML).” Dima’s article is useful to contrast NL prefetching and 8744 from NL batch sorting and 2340/9115.

920093 | Dima_1 | PWhite_1 | PWhite_2 | PWhite_19 | Connect_3


PWhite: “Disable DML Request Sort (CUpdUtil::FDemandRowsSortedForPerformance)” Appears to be the counterpoint of 2332.

PWhite_1 | PWhite_3


Dima: “Disables both [NLoop Implicit Batch Sort {TF 2340} and NL Prefetching {TF 8744}], and not only on the Post Optimization, but the explicit Sort also.” PWhite: “Disable prefetch (CUpdUtil::FPrefetchAllowedForDML)”

Dima_1 | PWhite_1 | Halincrest_1

Hashing and Batching


Enables the use of a hash join for joins to column store indexes even when the join clause would normally be removed “during query normalization”.



Disables the bitmap associated with hash matching. This bitmap is used for “bit-vector filtering” and can reduce the amount of data written to TempDB during hash spills.



Full behavior and intended purpose unknown, but the PWhite post uses it in concert with 7498 to disable “optimized bitmaps”.



Full behavior and intended purpose unknown, but the PWhite post uses it in concert with 7497 to disable “optimized bitmaps”.


9347 Doc2014

BOL 2014: “Disables batch mode for sort operator. SQL Server 2016 introduces a new batch mode sort operator that boosts performance for many analytical queries.”


9349 Doc2014
BOL 2014: “Disables batch mode for top N sort operator. SQL Server 2016 introduces a new batch mode top sort operator that boosts performance for many analytical queries.”

Disables batch-mode sort operations.



Disables Batch Mode in Parallel Columnstore query plans. (Note that a plan using batch mode appears to require a recompile before the TF takes effect).


Phases and Timeouts


Dima: disables the logic that prunes the memo and prevents the optimization process from stopping due to “Good Enough Plan found”. Can significantly increase the amount of time, CPU, and memory used in the compilation process.



Skips “Search 1” phase of query optimization (if applicable), and only Search 0 and Search 2 execute.



Skip Trivial Plan optimization, essentially forcing entry into Full optimization for a query.

PWhite_5 | PWhite_6


Dima: increases the “timeout” value that the optimizer sets to 3072000 transformations. Normally, the optimizer sets its internal timeout value to something based on the complexity of the query.


Paul White once tweeted: “There’s never a good reason to use or promote that dangerous flag”



Dima: notes that 8788 appears to have a similar effect on the timeout as 8780, but he hasn’t yet been able to determine the difference in effect between 8788 and 8780.


Presumably Paul White’s tweet about 8780 (above) applies here as well.




Disables “Few Outer Rows” optimization that helps maximize parallelization of dimensional queries.


2335 Doc2014

BOL 2014: “Causes SQL Server to assume a fixed amount of memory is available during query optimization. It does not limit the memory SQL Server grants to execute the query. The memory configured for SQL Server will still be used by data cache, query execution and other consumers.”

KB: Causes the optimizer to generate plans that are “more conservative in terms of memory consumption when executing the query.” KB describes scenario where large values of “max server memory” may lead to inefficient plans.

2413549 | PWhite_7


KB: “Makes SQL Server consider internal data management memory overhead when calculating required memory for sort.” Can help avoid sort spills to tempdb when the estimate is otherwise accurate.



Ignore index hints that are specified in query/procedure.

Kalen_1 | SiebelPDF


Strongly encourages the optimizer to generate parallel plans. (Perhaps by setting the costing for parallel exchange operators to 0.)

PWhite_5 | PWhite_8 | Machanic_1


Prevents the optimizer from using “performance spools’ (either table or index) in a query plan.

2962767 | CSS_2 | PWhite_9 | Connect_1 | Machanic_2 (near the end)


Force optimizer to use an Eager Spool when it needs Halloween Protection

PWhite_10 | PWhite_11


Disables all “other” (besides index and join) hints. This includes the OPTION clause. From Database-Wiki (but I suspect originally from a Khen book): “By running all three (8602, 8755, and 8722) flags, you can disable all hints in a query.”

SQLMag_1 | Database-Wiki


Whatever else it does, one effect is to disable the “rowset sharing” optimization described in the PWhite miniseries.

PWhite_11 | PWhite_12


Disables all join hints.



PWhite desc 1: “A [workaround to the MERGE bug described] is to apply 8758 – unfortunately this disables a number of optimisations, not just the one above, so it’s not really recommended for long term use.” PWhite 2: “Disable rewrite to a single operator plan (CPhyOp_StreamUpdate::PqteConvert)”

PWhite_13 | PWhite_1


PWhite: “Undocumented trace flag 8790 forces a wide update plan for any data-changing query (remember that a wide update plan is always possible).”

956718 | PWhite_14 | PWhite_15


Ballantyne SQLBits: “Disable non-sargable pushed predicates.” Prohibits the optimizer from pushing residual predicates down into “access method” iterators (i.e. seeks and scans).

PWhite_16 | PWhite_17 | DBally_2 | Connect_2


Sets a row limit (based on cardinality estimates) that controls whether a bulk insert is attempted or not (assuming conditions are met for a bulk insert). Introduced as a workaround for memory errors encountered with bulk insert.


Fixes and Past Relevance

These flags either are old and irrelevant for modern builds, appear only in CTPs, or enable a fix in a CU but are baselined in a later service pack or release.

Found a reference to it (via in Ken Henderson’s Guru’s Guide to Transact-SQL, page 503: “Disables query parallelism”.

In SQL 2000, apparently would have the same effect as OPTION(KEEPFIXED PLAN).



(Needs investigation) Turns back behavior to SQL 2000 SP3 after a SP4 installation, this allows the optimizer to choose an index seek when comparing numeric columns or numeric constants that are of different precision or scale; else would have to change schema/code.