This is my second post discussing some query optimizer undocumented trace flags (you can read the first one, “Inside the Query Optimizer Memo Structure”, here). Although is not the purpose of this post to discuss the entire optimization process or go into detail on any of the optimization phases, you can read more about it in my book Inside the SQL Server Query Optimizer, which you can download for free from the simple-talk website. And same as before, please bear in mind that all these trace flags are undocumented and unsupported, and should not be used on a production environment. You can use them as a way to explore and understand how the query optimizer works.
As mentioned in my previous post, you will first have to enable the trace flag 3604 to redirect the trace output to the client executing the command, in this case to the Messages tab in SQL Server Management Studio.
DBCC TRACEON(3604)
I’ll start with three trace flags which display logical and physical trees used during the optimization process. First, trace flag 8605 will display the query initial tree representation created by SQL Server. Test if by running
SELECT e.EmployeeID FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID OPTION (RECOMPILE, QUERYTRACEON 8605)
It will show the following output
*** Converted Tree: *** LogOp_Project QCOL: [e].EmployeeID LogOp_Join LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001 LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [e].EmployeeID ScaOp_Identifier QCOL: [s].SalesPersonID AncOp_PrjList
Trace flag 8606 will display additional logical trees used during the optimization process. Run
SELECT e.EmployeeID FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID OPTION (RECOMPILE, QUERYTRACEON 8606)
The output shows several different logical trees: input tree, simplified tree, join-collapsed tree, tree before project normalization, and tree after project normalization. These trees will include logical operators only. Part of the output is shown next.
*** Input Tree: *** LogOp_Project QCOL: [e].EmployeeID LogOp_Select LogOp_Join LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001 LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003 ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [e].EmployeeID ScaOp_Identifier QCOL: [s].SalesPersonID AncOp_PrjList *******************
*** Simplified Tree: *** LogOp_Join LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001 LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [s].SalesPersonID ScaOp_Identifier QCOL: [e].EmployeeID ******************* *** Join-collapsed Tree: *** LogOp_Join LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001 LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [s].SalesPersonID ScaOp_Identifier QCOL: [e].EmployeeID ******************* *** Tree Before Project Normalization *** LogOp_Join LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001 LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [s].SalesPersonID ScaOp_Identifier QCOL: [e].EmployeeID ***************************************** *** Tree After Project Normalization *** LogOp_Join LogOp_Get TBL: HumanResources.Employee(alias TBL: e) HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001 LogOp_Get TBL: Sales.SalesPerson(alias TBL: s) Sales.SalesPerson TableID=1042102753 TableReferenceID=0 IsRow: COL: IsBaseRow1003 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [s].SalesPersonID ScaOp_Identifier QCOL: [e].EmployeeID **************************************** *** Stop search, level 1 ***
One interesting example is seeing how a tree is simplified when the query optimizer can detect a contradiction during the simplification phase. The purpose of the simplification stage is to reduce the query tree into a simpler form in order to make the optimization process easier. Contradiction detection is one of several possible simplifications. Following on an example on my book and this blog post, run the following query
SELECT * FROM HumanResources.Employee WHERE VacationHours > 300 OPTION (RECOMPILE, QUERYTRACEON 8606)
Part of the output is next
*** Input Tree: *** LogOp_Project QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate LogOp_Select LogOp_Get TBL: HumanResources.Employee HumanResources.Employee TableID=901578250 TableReferenceID=0 IsRow: COL: IsBaseRow1001 ScaOp_Comp x_cmpGt ScaOp_Identifier QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=300) AncOp_PrjList ******************* *** Simplified Tree: *** LogOp_ConstTableGet (0) COL: Chk1000 COL: IsBaseRow1001 QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate ******************* *** Join-collapsed Tree: *** LogOp_ConstTableGet (0) COL: Chk1000 COL: IsBaseRow1001 QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate ******************* *** Tree Before Project Normalization *** LogOp_ConstTableGet (0) COL: Chk1000 COL: IsBaseRow1001 QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate ***************************************** *** Tree After Project Normalization *** LogOp_ConstTableGet (0) COL: Chk1000 COL: IsBaseRow1001 QCOL: [AdventureWorks].[HumanResources].[Employee].EmployeeID QCOL: [AdventureWorks].[HumanResources].[Employee].NationalIDNumber QCOL: [AdventureWorks].[HumanResources].[Employee].ContactID QCOL: [AdventureWorks].[HumanResources].[Employee].LoginID QCOL: [AdventureWorks].[HumanResources].[Employee].ManagerID QCOL: [AdventureWorks].[HumanResources].[Employee].Title QCOL: [AdventureWorks].[HumanResources].[Employee].BirthDate QCOL: [AdventureWorks].[HumanResources].[Employee].MaritalStatus QCOL: [AdventureWorks].[HumanResources].[Employee].Gender QCOL: [AdventureWorks].[HumanResources].[Employee].HireDate QCOL: [AdventureWorks].[HumanResources].[Employee].SalariedFlag QCOL: [AdventureWorks].[HumanResources].[Employee].VacationHours QCOL: [AdventureWorks].[HumanResources].[Employee].SickLeaveHours QCOL: [AdventureWorks].[HumanResources].[Employee].CurrentFlag QCOL: [AdventureWorks].[HumanResources].[Employee].rowguid QCOL: [AdventureWorks].[HumanResources].[Employee].ModifiedDate
In this case the query optimizer makes use of an existing check constraint to conclude that no records qualify for the predicate VacationHours > 300, replacing the entire tree with a LogOp_ConstTableGet logical operator. If you try displaying an output tree, as discussed next, you will get a PhyOp_ConstTableScan physical operator, and you will get a constant scan operator on the final execution plan. You can see a different behavior if you try the same query with a predicate like VacationHours > 20.
Trace flag 8607 shows the optimization output tree. Try the following sentence
SELECT e.EmployeeID FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID OPTION (RECOMPILE, QUERYTRACEON 8607)
Notice that this time we have physical operators and the output tree is closer to the final execution plan.
**************************************** *** Output Tree: *** PhyOp_Apply lookup TBL: HumanResources.Employee (0) (x_jtInner) PhyOp_Range TBL: Sales.SalesPerson(alias TBL: s)(2) ASC Bmk ( QCOL: [s].SalesPersonID) IsRow: COL: IsBaseRow1003 PhyOp_Range TBL: HumanResources.Employee(alias TBL: e)(1) ASC Bmk ( QCOL: [e].EmployeeID) IsRow: COL: IsBaseRow1001 ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [s].SalesPersonID ScaOp_Identifier QCOL: [e].EmployeeID ******************** ** Query marked as Cachable ********************
Trace flag 8675 shows the query optimization phases for a specific optimization along with some other information like cost estimation, tasks, etc. You may want to test it with complex queries to see different optimization phases like in the following example
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City, SP.Name AS State, CR.Name AS CountryRegion FROM Person.Contact AS C JOIN Sales.Individual AS I ON C.ContactID = I.ContactID JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID JOIN Person.Address AS A ON A.AddressID = CA.AddressID JOIN Person.StateProvince SP ON SP.StateProvinceID = A.StateProvinceID JOIN Person.CountryRegion CR ON CR.CountryRegionCode = SP.CountryRegionCode ORDER BY I.CustomerID OPTION (RECOMPILE, QUERYTRACEON 8675)
It shows the following output
End of simplification, time: 0.003 net: 0.003 total: 0.003 net: 0.003 end exploration, tasks: 275 no total cost time: 0.005 net: 0.005 total: 0.009 net: 0.009 end exploration, tasks: 642 no total cost time: 0.003 net: 0.003 total: 0.012 net: 0.012 end search(0), cost: 9.04 tasks: 681 time: 0 net: 0 total: 0.013 net: 0.013 end exploration, tasks: 1465 Cost = 9.04 time: 0.004 net: 0.004 total: 0.017 net: 0.017 end exploration, tasks: 2518 Cost = 9.04 time: 0.007 net: 0.007 total: 0.024 net: 0.024 end search(1), cost: 5.86879 tasks: 2609 time: 0 net: 0 total: 0.025 net: 0.025 end exploration, tasks: 2610 Cost = 5.86879 time: 0 net: 0 total: 0.025 net: 0.025 end exploration, tasks: 5170 Cost = 5.86879 time: 0.021 net: 0.021 total: 0.047 net: 0.047 end search(1), cost: 5.86248 tasks: 5469 time: 0.001 net: 0.001 total: 0.048 net: 0.048 end exploration, tasks: 5756 Cost = 5.86248 time: 0.001 net: 0.001 total: 0.05 net: 0.05 end exploration, tasks: 6434 Cost = 5.86248 time: 0.005 net: 0.005 total: 0.055 net: 0.055 end search(2), cost: 5.84575 tasks: 7092 time: 0.004 net: 0.004 total: 0.059 net: 0.059 End of post optimization rewrite, time: 0 net: 0 total: 0.059 net: 0.059 End of query plan compilation, time: 0 net: 0 total: 0.06 net: 0.06
Note the optimization phases search(0), search(1) and search(2). Also, as mentioned in my book, you need at least three tables to qualify for search 0, so the following query will go directly to search 1 (you can also test this with trace flag 2372 as described later):
SELECT * FROM HumanResources.Employee WHERE ManagerID = 12 OPTION (RECOMPILE, QUERYTRACEON 8675)
Trace flags 2372 and 2373 show memory utilization during the optimization process but we can also use them to gain information about the activities the query optimizer is performing for a particular query. Trace flag 2372 shows memory utilization during the different optimization stages. Same as before, more complicated queries will show more optimization stages.
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City, SP.Name AS State, CR.Name AS CountryRegion FROM Person.Contact AS C JOIN Sales.Individual AS I ON C.ContactID = I.ContactID JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID JOIN Person.Address AS A ON A.AddressID = CA.AddressID JOIN Person.StateProvince SP ON SP.StateProvinceID = A.StateProvinceID JOIN Person.CountryRegion CR ON CR.CountryRegionCode = SP.CountryRegionCode ORDER BY I.CustomerID OPTION (RECOMPILE, QUERYTRACEON 2372)
The output is next
Memory before NNFConvert: 13 Memory after NNFConvert: 14 Memory before project removal: 15 Memory after project removal: 15 Memory before simplification: 15 Memory after simplification: 35 Memory before heuristic join reordering: 35 Memory after heuristic join reordering: 46 Memory before project normalization: 46 Memory after project normalization: 46 Memory before stage TP: 46 Memory after stage TP: 81 Memory before stage QuickPlan: 81 Memory after stage QuickPlan: 144 Memory before stage Full: 144 Memory after stage Full: 156 Memory before copy out: 156 Memory after copy out: 157
Notice that “stage TP” or transaction processing phase is the same as search(0) shown before with trace flag 8675. In the same way, “stage QuickPlan” is the search(1) and “stage Full” is search(2).
Trace flag 2373 shows memory utilization while applying optimization rules and deriving properties.
SELECT e.EmployeeID FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS s ON e.EmployeeID = s.SalesPersonID OPTION (RECOMPILE, QUERYTRACEON 2373)
Part of the output is next
Memory before rule IJtoIJSEL: 14 Memory after rule IJtoIJSEL: 14 Memory before rule MatchGet: 14 Memory after rule MatchGet: 14 Memory before rule MatchGet: 14 Memory after rule MatchGet: 14 Memory before rule JoinToIndexOnTheFly: 14 Memory after rule JoinToIndexOnTheFly: 14 Memory before rule JoinCommute: 14 Memory after rule JoinCommute: 14 Memory before rule JoinToIndexOnTheFly: 14 Memory after rule JoinToIndexOnTheFly: 14 Memory before rule JNtoIdxLookup: 14
Finally, trace 8757 can be used to skip the trivial plan optimization and basically force a full optimization. As a reminder, the trivial plan optimization is used for very simple queries that don’t require any cost estimation decision. For example, the following query will produce a trivial plan, which you can verify by looking at the optimization level or StatementOptmLevel property in your execution plan
SELECT * FROM dbo.DatabaseLog
By applying trace flag 8757, the trivial plan optimization will be skipped as you can verify by running the following query and once again verifying the optimization level property.
SELECT * FROM dbo.DatabaseLog OPTION (QUERYTRACEON 8757)