Compilation Informational¶
Flags in this category fall into the below groupings:
- 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.
- Phases and Rules –> The phases entered by the optimizer and the rules it uses in those phases.
- Miscellaneous –> Resource usage, operator-specific info, and other “miscellaneous” info.
- 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. |
| … | |
| Trees/Memos | |
| 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. |
| … | |
| Phases/Rules | |
| 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” |
| … | |
| Misc | |
| 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. |
| … | |
| Fix/PastRel |
Trees and Memos¶
- 7352 (Info)
The final query tree after Post-optimization re-write.
- 8605 (Info)
The initial logical tree (the input into query optimization). (Paul also calls this the “converted tree” in Part 4 of his series)
- 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”
- 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
- 8612 (Info)
Dima: adds cardinality info to the various trees produced by flags 8605, 8606, and 8607.
- 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.”
- 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.
- 8619 (Info)
PWhite: “Apply rule with description”; Dima: “Show Applied Transformation Rules”
- 8628 (Info)
When used with 8666 (see below), causes extra information about the transformation rules applied to be put into the XML showplan.
- 8675 (Info)
Display query optimization phases, along with info (timing, costs, etc) about each phase.
- 8739 (Info)
Dima: “Group Optimization Info”
Miscellaneous¶
- 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 SQLService.se
- 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.
- 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.
- 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.