Some Query Hints: Undocumented, But Interesting

2023-05-03

It's a common misconception, even among some knowledgeable SQL folks, that the SQL Server query optimizer evaluates different ways of executing a query and goes with the query plan that yields the lowest estimated cost. As is often the case with misconceptions, this one is not so wrong as oversimplified. The SQL Server query optimizer breaks queries into subsets called "groups" and optimizes the groups separately. It then assembles the optimized groups into a single query that, when executed, returns the rows the query writer asked for. (Hopefully, these are also the rows they wanted.)

Panoramic view of multiple database is placed on Relational database tables.

In most cases, the query optimizer does an excellent job. However, in some cases, examining the query plan in the Management Studio can provide insights into how SQL Server might be provided with additional resources to help it execute a query faster.

Today we'll look at some techniques of no practical value unless you are curious about digging deeper into SQL Server to see how it works.

Some Undocumented Query Hints

In the world of Microsoft, "undocumented" usually means something more like "not officially documented." In any case, a few query hints let us examine more closely what the query optimizer has accomplished in preparing an execution plan.

We'll start by using the same query used to examine batch processing in the blog XXXXXXXXXXXX.

DBCC TRACEON (3604)
SELECT d.CalendarMonth
,SUM(fs.SalesAmount)
--,SUM(fs.ReturnAmount)
--,SUM(fs.SalesQuantity)
--,SUM(fs.ReturnQuantity)
FROM DimDate d
JOIN FactSales fs
ON fs.DateKey = d.DateKey
GROUP BY d.CalendarMonth
OPTION(RECOMPILE, QUERYTRACEON 8605);

The magical DBCC TRACEON (3604) must be executed to output these traces. Some people include QUERYTRACEON 3604 as a query hint, but since DBCC TRACEON (3604) has session-level scope, we need only run it once, and it will remain set for the duration of our session.

Note that the query hint includes "RECOMPILE." If we did not do this, the trace would only generate information the first time the query was run. On subsequent executions, the query would be cached, and there would be no optimizer output.

Trace 8605

The output for trace 8605 shows the input tree, laying out the logical operations implied by the query.

*** Converted Tree: ***
 LogOp_Project QCOL: [d].CalendarMonth COL: Expr1002
 LogOp_GbAgg OUT(QCOL: [d].CalendarMonth,COL: Expr1002 ,) BY(QCOL: [d].CalendarMonth,)
 LogOp_Project
 LogOp_Join
 LogOp_Get TBL: DimDate(alias TBL: d) DimDate TableID=610101214 TableReferenceID=0 IsRow: COL: IsBaseRow1000
 LogOp_Get TBL: FactSales(alias TBL: fs) FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1001
 ScaOp_Comp x_cmpEq
 ScaOp_Identifier QCOL: [fs].DateKey
 ScaOp_Identifier QCOL: [d].Datekey
 AncOp_PrjList
 AncOp_PrjList
 AncOp_PrjEl COL: Expr1002
 ScaOp_AggFunc stopSum Transformed
 ScaOp_Identifier QCOL: [fs].SalesAmount
 AncOp_PrjList

TRACE 8606

Trace 8606 shows intermediate steps in the processing of the input tree

*** Input Tree: ***
 LogOp_Project QCOL: [d].CalendarMonth COL: Expr1002 
LogOp_GbAgg OUT(QCOL: [d].CalendarMonth,COL: Expr1002 ,) BY(QCOL: [d].CalendarMonth,)
 LogOp_Project
LogOp_Select
LogOp_Join
LogOp_Get TBL: DimDate(alias TBL: d) DimDate TableID=610101214 TableReferenceID=0 IsRow: COL: IsBaseRow1000 
LogOp_Get TBL: FactSales(alias TBL: fs) FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1)
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [fs].DateKey
ScaOp_Identifier QCOL: [d].Datekey
AncOp_PrjList 
AncOp_PrjList 
AncOp_PrjEl COL: Expr1002 
ScaOp_AggFunc stopSum Transformed
ScaOp_Identifier QCOL: [fs].SalesAmount
AncOp_PrjList 
*******************

*** Simplified Tree: ***
LogOp_GbAgg OUT(QCOL: [d].CalendarMonth,COL: Expr1002 ,) BY(QCOL: [d].CalendarMonth,)
LogOp_Join
LogOp_Get TBL: DimDate(alias TBL: d) DimDate TableID=610101214 TableReferenceID=0 IsRow: COL: IsBaseRow1000 
LogOp_Get TBL: FactSales(alias TBL: fs) FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [d].Datekey
ScaOp_Identifier QCOL: [fs].DateKey
AncOp_PrjList 
AncOp_PrjEl COL: Expr1002 
ScaOp_AggFunc stopAccum
ScaOp_Identifier QCOL: [fs].SalesAmount
*******************

*** Join-collapsed Tree: ***
LogOp_GbAgg OUT(QCOL: [d].CalendarMonth,COL: Expr1002 ,) BY(QCOL: [d].CalendarMonth,)
LogOp_Join
LogOp_Get TBL: DimDate(alias TBL: d) DimDate TableID=610101214 TableReferenceID=0 IsRow: COL: IsBaseRow1000 
LogOp_Get TBL: FactSales(alias TBL: fs) FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [d].Datekey
ScaOp_Identifier QCOL: [fs].DateKey
AncOp_PrjList 
AncOp_PrjEl COL: Expr1002 
ScaOp_AggFunc stopAccum
ScaOp_Identifier QCOL: [fs].SalesAmount
*******************

*** Tree Before Project Normalization ***
LogOp_GbAgg OUT(QCOL: [d].CalendarMonth,COL: Expr1002 ,) BY(QCOL: [d].CalendarMonth,)
LogOp_Join
LogOp_Get TBL: DimDate(alias TBL: d) DimDate TableID=610101214 TableReferenceID=0 IsRow: COL: IsBaseRow1000 
LogOp_Get TBL: FactSales(alias TBL: fs) FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [d].Datekey
ScaOp_Identifier QCOL: [fs].DateKey
AncOp_PrjList 
AncOp_PrjEl COL: Expr1002 
ScaOp_AggFunc stopAccum
ScaOp_Identifier QCOL: [fs].SalesAmount
*****************************************

*** Tree After Project Normalization ***
LogOp_GbAgg OUT(QCOL: [d].CalendarMonth,COL: Expr1002 ,) BY(QCOL: [d].CalendarMonth,)
LogOp_Join
LogOp_Get TBL: DimDate(alias TBL: d) DimDate TableID=610101214 TableReferenceID=0 IsRow: COL: IsBaseRow1000 
LogOp_Get TBL: FactSales(alias TBL: fs) FactSales TableID=1602104748 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [d].Datekey
ScaOp_Identifier QCOL: [fs].DateKey
AncOp_PrjList 
AncOp_PrjEl COL: Expr1002 
ScaOp_AggFunc stopAccum
ScaOp_Identifier QCOL: [fs].SalesAmount

TRACE 8607

Trace 8607 shows the resulting output tree. Note that the operations labeled "LogOp" have disappeared and "PhyOp" operations have taken their place.

*** Output Tree: ***
Exchange Start
PhyOp_ExecutionModeAdapter(BatchToRow)
PhyOp_HashGbAgg(batch)<global> Eager Distinct 
PhyOp_Concat (batch)
OUTPUT(COL: IsBaseRow1000 QCOL: [d].Datekey QCOL: [d].CalendarMonth QCOL: [fs].DateKey COL: partialagg1003 )
CHILD(COL: IsBaseRow1000 QCOL: [d].Datekey QCOL: [d].CalendarMonth QCOL: [fs].DateKey COL: partialagg1003 )
CHILD(COL: IsBaseRow1000 QCOL: [d].Datekey QCOL: [d].CalendarMonth QCOL: [fs].DateKey COL: partialagg1003 )
PhyOp_HashJoinx_jtInner (batch)(QCOL: [fs].DateKey)
= (QCOL: [d].Datekey)
PhyOp_HashGbAgg(batch)<exact local> Eager Distinct 
PhyOp_Range TBL: FactSales(alias TBL: fs)(1) ASC Bmk ( QCOL: [fs].SalesKey) IsRow: COL: IsBaseRow1001 
AncOp_PrjList 
AncOp_PrjEl COL: partialagg1003 
ScaOp_AggFunc stopAccum
ScaOp_Identifier QCOL: [fs].SalesAmount
PhyOp_Range TBL: DimDate(alias TBL: d)(1) ASC Bmk ( QCOL: [d].Datekey) IsRow: COL: IsBaseRow1000 
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [d].Datekey
ScaOp_Identifier QCOL: [fs].DateKey
PhyOp_ExecutionModeAdapter(RowToBatch)
PhyOp_Apply lookup TBL: DimDate (0) (x_jtInner)
PhyOp_ExecutionModeAdapter(BatchToRow)
PhyOp_HashGbAgg(batch)<exact local> Eager Distinct [** 4 **]
PhyOp_Range TBL: DimDate(alias TBL: d)(1) ASC Bmk ( QCOL: [d].Datekey) IsRow: COL: IsBaseRow1000 
ScaOp_Comp x_cmpEq[** 11 **]
AncOp_PrjList 
AncOp_PrjEl COL: Expr1002 
ScaOp_AggFunc stopAccumNull
ScaOp_Identifier COL: partialagg1003

TRACE 8675

Trace 8675 doesn't provide much insight into the optimization process, but the report shows the estimated cost of alternative query plans.

End of simplification, time: 0 net: 0 total: 0 net: 0
end exploration, tasks: 133 no total cost time: 0.001 net: 0.001 total: 0 net: 0.001
end exploration, tasks: 134 no total cost time: 0 net: 0 total: 0 net: 0.001
end exploration, tasks: 659 no total cost time: 0.007 net: 0.007 total: 0 net: 0.008
end exploration, tasks: 662 no total cost time: 0 net: 0 total: 0 net: 0.009
end exploration, tasks: 674 no total cost time: 0 net: 0 total: 0 net: 0.009
end search(1), cost: 29.225 tasks: 674 time: 0 net: 0 total: 0 net: 0.009
end exploration, tasks: 675 Cost = 29.225 time: 0 net: 0 total: 0 net: 0.009
end exploration, tasks: 676 Cost = 29.225 time: 0 net: 0 total: 0 net: 0.009
end exploration, tasks: 1353 Cost = 29.225 time: 0.002 net: 0.002 total: 0 net: 0.011
end exploration, tasks: 1356 Cost = 29.225 time: 0 net: 0 total: 0 net: 0.011
end exploration, tasks: 1422 Cost = 29.225 time: 0 net: 0 total: 0 net: 0.011
end search(1), cost: 17.1178 tasks: 1422 time: 0 net: 0 total: 0 net: 0.011
End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0.012
End of query plan compilation, time: 0 net: 0 total: 0 net: 0.012

Note that two different estimated costs have been highlighted in red. Not surprisingly, when we look at the actual execution plan, the smaller of the two appears as the estimated subtree cost for the SELECT icon.

Some Other Trace Flags

2372
2373

These traces provide information on the memory usage of the query tree logical optimizations:

8608
8609

Trace flags 8608 and 8609 provide further information about the query optimizer "memo." The memo is nothing more than a chalkboard on which the SQL Server writes pertinent information as it progresses through query optimization.

Conclusions

SQL Server continues to provide developers with more and deeper information on the query optimizer. This allows developers to gain insight, not to mention have more fun.

Find what you're looking for with SQL Training and Courses.

This piece was originally posted on October 10, 2021, and has been refreshed with updated styling.

Written by Dan Buskirk

"The pleasures of the table belong to all ages." Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.