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:
- Disk IO Optimization –> Flags whose goal is to improve Disk IO performance (often by promoting sequential IO)
- Hashing and Batching –> Flags that modify hashing operations and flags that adjust batch mode processing
- Phases and Timeouts –> Flags that alter the phases the optimizer enters or its timeout values for short-circuiting phase entry.
- Miscellaneous –> Other, miscellaneous flags.
- 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) |
| … | |
| Hash/Batch | |
| 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. |
| … | |
| Phases/TOs | |
| 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! |
| … | |
| Misc | |
| 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. |
| … | |
| Fix/PastRel | |
| 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¶
- 2332
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.
- 8633
PWhite: “Enable prefetch (CUpdUtil::FPrefetchAllowedForDML and CPhyOp_StreamUpdate::FDoNotPrefetch)”
- 8738
(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
- 8795
PWhite: “Disable DML Request Sort (CUpdUtil::FDemandRowsSortedForPerformance)” Appears to be the counterpoint of 2332.
- 9115
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)”
Hashing and Batching¶
- 2441
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”.
- 7359
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.
- 7497
Full behavior and intended purpose unknown, but the PWhite post uses it in concert with 7498 to disable “optimized bitmaps”.
- 7498
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.”
- 9358
Disables batch-mode sort operations.
- 9453
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¶
- 8671
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.
- 8677
Skips “Search 1” phase of query optimization (if applicable), and only Search 0 and Search 2 execute.
- 8757
Skip Trivial Plan optimization, essentially forcing entry into Full optimization for a query.
- 8780
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.
Warning
Paul White once tweeted: “There’s never a good reason to use or promote that dangerous flag”
- 8788
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.
Warning
Presumably Paul White’s tweet about 8780 (above) applies here as well.
Miscellaneous¶
- 2329
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.
- 7470
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.
- 8649
Strongly encourages the optimizer to generate parallel plans. (Perhaps by setting the costing for parallel exchange operators to 0.)
- 8690
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)
- 8722
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.”
- 8746
Whatever else it does, one effect is to disable the “rowset sharing” optimization described in the PWhite miniseries.
- 8755
Disables all join hints.
- 8758
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)”
- 8790
PWhite: “Undocumented trace flag 8790 forces a wide update plan for any data-changing query (remember that a wide update plan is always possible).”
- 9130
Ballantyne SQLBits: “Disable non-sargable pushed predicates.” Prohibits the optimizer from pushing residual predicates down into “access method” iterators (i.e. seeks and scans).
- 9348
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.
- 8687
- Found a reference to it (via books.google.com) in Ken Henderson’s Guru’s Guide to Transact-SQL, page 503: “Disables query parallelism”.
- 8720
In SQL 2000, apparently would have the same effect as OPTION(KEEPFIXED PLAN).
- 9059
(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.