Compilation Informational

Flags in this category fall into the below groupings:

  1. Trees and Memos –> Flag prints one or more of the “trees” constructed during the query optimization process, or the state of the memo at a given point in the optimization process.
  2. Phases and Rules –> The phases entered by the optimizer and the rules it uses in those phases.
  3. Miscellaneous –> Resource usage, operator-specific info, and other “miscellaneous” info.
  4. Fixes and Past Relevance –> Fix flags and flags which are no longer relevant.

All flags in this category are informational (the redundant “Info” tag has been retained for the sake of screenshots and copy-pasting), as far as we know.

Short Descriptions

Flag Short Description
See Also  
* 2363 New CE: Outputs info about stats used and resulting cardinality estimates.
8721 Prints info to error log when AutoStat occurs.
9204 Prints which stats objects the QO found interesting and then fully loaded.
9292 Prints which stats objects the QO found interesting and loaded the header.
7352 Final query tree after the post-optimization rewrite phase.
8605 Initial logical tree (input into actual optimization). AKA “converted tree”.
8606 Multiple trees: Input, Simplified, Join-collapsed, and before/after Proj Norm
8607 Optimization output tree, before post-optimization rewrite phase.
8608 Initial Memo structure
8612 Adds cardinality info to trees produced by 8605, 8606, 8607.
8615 Final Memo structure.
8620 Adds Memo arguments to 8619 (which outputs the transformation rules applied)
8621 Adds trees to the transformation rule printout from 8619.
2372 Prints memory utilization for each entered optimization phase.
2373 Prints memory utilization before/after each applied optimizer rule.
8609 Prints task and operation type counts
8619 Prints transformation rules applied during optimization.
8628 Places transformation rules used into the query plan. (8666 exposes in XML)
8675 Prints the query optimization phases entered.
8739 Dima: “Group Optimization Info”
205 Reports to error log when recompile occurs due to auto-update of stats.
445 Full func unknown; prints sql text from compilations to error log.
2315 Aaron discovered: appears to output mem allocations during compilation
2318 Aaron discovered: maybe info about join reordering?
2336 Aaron discovered: maybe connects cached page likelihoods, mem status, and costing?
2398 Aaron discovered: outputs info about “Smart Seek costing”
7357 Various info about hash join and match operators.
8666 Causes useful info present in the binary query plan to be exposed in the XML.
8719 (Maybe past relevance) Apparently outputs IO prefetch for NL joins/bookmarks.

Trees and Memos

7352 (Info)

The final query tree after Post-optimization re-write.

PWhite_4 | PWhite_5 | Dima_5 | Dima_6

8605 (Info)

The initial logical tree (the input into query optimization). (Paul also calls this the “converted tree” in Part 4 of his series)

PWhite_1 | Nevarez_1

8606 (Info)

Displays additional logical trees, including the Input Tree, the Simplified Tree, the Join-collapsed Tree, the “Tree before Project Normalization”, and the “Tree after Project Normalization”

PWhite_2 | Dima_1 | Nevarez_1

8607 (Info)

Displays the optimization output tree, before Post-optimization rewrite. Has a “Query marked as cachable” note if the plan can be cached.

PWhite_3 | PWhite_6 | PWhite_7 | PWhite_8 | PWhite_9 | Dima_3 | Dima_6 | Nevarez_1

8608 (Info)

Shows the initial Memo structure

PWhite_3 | Dima_3 | Dima_4 | Nevarez_2

8612 (Info)

Dima: adds cardinality info to the various trees produced by flags 8605, 8606, and 8607.

PWhite_8 | Dima_3 | Dima_7 | Dima_8

8615 (Info)

Show the final Memo structure

PWhite_3 | Dima_3 | Dima_4 | Dima_9

8620 (Info)

PWhite: “Add memo arguments to 8619”


8621 (Info)

PWhite: “Rule with resulting tree” (use with 8619)


Phases and Rules

2372 (Info)

Nevarez: “shows memory utilization during the different optimization stages.”

Nevarez_1 | Dima_8

2373 (Info)

Shows memory utilization before and after various optimizer rules are applied (e.g. IJtoIJsel). Provides a way to “trace” what rules are used when optimizing a query.

PWhite_4 | Dima_2 | Dima_8 | Dima_9

8609 (Info)

PWhite: “Task and operation type counts”

PWhite_4 | Dima_10

8619 (Info)

PWhite: “Apply rule with description”; Dima: “Show Applied Transformation Rules”

PWhite_4 | PWhite_10 | Dima_8

8628 (Info)

When used with 8666 (see below), causes extra information about the transformation rules applied to be put into the XML showplan.

Dima_11 | PWhite_11

8675 (Info)

Display query optimization phases, along with info (timing, costs, etc) about each phase.

PWhite_3 | PWhite_12 | PWhite_13 | PWhite_9

8739 (Info)

Dima: “Group Optimization Info”



205 Doc2014 (Info)

BOL 2014: “Reports to the error log when a statistics-dependent stored procedure is being recompiled as a result of auto-update statistics.” Its appearance in Randal-SQL-SDB407 indicates that this flag is fairly old.


445 (Info)
Full functionality unknown. Prints “Compile issued:” and then the text of the sql statement being compiled to the SQL error log. Personally confirmed that this still works in SQL 2014 even though it appears to be a very old trace flag. Discovered via
2315 (Info)
Aaron: personally discovered. Seems to output memory allocations taken during the compilation process (and maybe the plan as well? “PROCHDR”), as well as memory broker states & values at the beginning and end of compilation.
2318 (Info)
Aaron: personally discovered. I’ve only seen one type of output so far: “Optimization Stage: HEURISTICJOINREORDER”. Maybe useful in combo with other compilation trace flags to see the timing of join reordering?
2336 (Info)
Aaron: personally discovered. Appears to tie memory info and cached page likelihoods with costing.
2398 (Info)
Aaron: personally discovered. Outputs info about “Smart Seek costing”: e.g.: “Smart seek costing (75.2) :: 1.34078e+154 , 1”.
7357 (Info)

Info re: hash operators, including role reversal, recursion levels, Unique Hash optimization usage, hash-related bitmap, etc. For parallel query plans, 7357 does NOT send output to the console window. However, output to the SQL Server error log can be enabled by enabling 3605.

Dima_12 | PWhite_4

8666 (Info)

Causes some useful info (including stat object thresholds) already present in the internal representation of a plan to be included in the XML plan output.

Fabiano_1 | DBally_1 | PWhite_14 | PWhite_15

8719 (Info)

In SQL 2000, apparently would show IO prefetch on loop joins and bookmarks. I (Aaron) was unable to replicate the query plan behavior on SQL 2012 using the same test, so this flag may be obsolete. (Would be really nice if it wasn’t!)


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.