Benjamin Nevarez Rotating Header Image

More Undocumented Query Optimizer Trace Flags

 

clip_image001

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)

About the author

Benjamin Nevarez Benjamin Nevarez is a database professional based in Los Angeles, CA, and author of "Inside the SQL Server Query Optimizer". He has also contributed to other SQL Server books including "SQL Server 2012 Internals". Benjamin has 20 years of experience with relational databases and has been working with SQL Server since version 6.5. He holds a Master’s Degree in Computer Science and has been a speaker at many SQL Server conferences, including the PASS Summit and SQL Server Connections. Benjamin’s blog is at http://www.benjaminnevarez.com, can be reached by e-mail at admin at benjaminnevarez dot com, on twitter at @BenjaminNevarez and on .

7 Comments

  1. Jungsun Kim says:

    Owesome! Thank you for the great information :)

  2. [...] 28.04.2012 Benjamin Nevarez blog — Inside the Query Optimizer Memo Structure Benjamin Nevarez blog — More Undocumented Query Optimizer Trace Flags Paul White blog — Query Optimizer Deep Dive – Part 1 PDF   [...]

  3. [...] Optimizer Deep Dive More Undocumented Query Optimizer Trace Flags PDF   Теги:estimates, optimizer, SqlServer, undocumented « RowGoal и [...]

  4. [...] related converts. QueryTraceON is blogged about in several places – See Benjamin blog (Query Optimizer Trace Flags [...]

  5. […] Flag : 2372 Function: Displays memory utilization during the optimization […]

Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>