By: Eli Leiba | Comments | Related: > Performance Tuning
Problem
The requirement is to get a detailed SQL Server internal query cardinality estimator component plan information report for a given valid query. The information should include call trees, statistics for each operation and applied operation rules available in SQL Server.
Solution
My solution involves creating a rather simple T-SQL stored procedure in the SQL Server master database, called dbo.usp_GetQueryEstimatorInternalInfo that has one parameter, a valid and tested query string.
The procedure concatenates the query parameter, a complex query hint (OPTION clause) consisting of five different trace flags along with a RECOMPILE hint, creating an extended query (with this hint). The combination of all of these flags ensures that all the detailed, internal information will be printed to the Messages tab in SQL Server Management Studio for further examination.
Here is a description of the trace flags:
Trace Flag | Operation |
---|---|
2363 | Enable debugging output that shows what statistics objects were used by the cardinality estimator during query compilation in SQL Server 2014. |
3604 | Redirects trace output to the client so it appears in the SSMS messages tab. |
8606 | Shows the LogOp_xxx functions call Trees. |
8612 | Adds extra Info to the trees output. |
8619 | Shows the applied transformation rules. |
The procedure dynamically executes the modified query, so all information is output to the Messages tab.
SQL Server Query Estimator
-- ================================================================================= -- Author: Eli Leiba -- Create date: 09-2018 -- Procedure Name: dbo.usp_GetQueryEstimatorInternalInfo -- Description: -- The procedure gets a string parameter containing a valid query -- concatenates some query trace flags to the query in order to -- display the internal cardinality estimator component functions -- call tree. -- ================================================================================== CREATE PROCEDURE dbo.usp_GetQueryEstimatorInternalInfo (@query NVARCHAR (3000)) AS BEGIN DECLARE @TSQL NVARCHAR (4000) = '' SET NOCOUNT ON SET @TSQL = CONCAT ( @query, ' option(recompile, ', ' querytraceon 2363, ', ' querytraceon 3604, ', ' querytraceon 8606, ', ' querytraceon 8612, ', ' querytraceon 8619) ' ) EXEC (@TSQL); SET NOCOUNT OFF END GO
Here is an example query we will use with the procedure:
SELECT p.productid, p.ProductName, c.CategoryName FROM Northwind.dbo.products p, Northwind.dbo.Categories c WHERE p.categoryid = c.CategoryID AND p.categoryid < 4
The procedure call looks like this. Note you have to specify the database in the FROM clause for each object.
exec master.dbo.usp_GetQueryEstimatorInternalInfo @query='SELECT p.productid, p.ProductName, c.CategoryName FROM Northwind.dbo.products p, Northwind.dbo.Categories c WHERE p.categoryid = c.CategoryID and p.categoryid < 4' GO
The results on my test machine are as follows. This is output to the Messages tab.
*** Input Tree: *** LogOp_Project QCOL: [p].ProductID QCOL: [p].ProductName QCOL: [c].CategoryName [ Card=0 ] LogOp_Select [ Card=0 ] LogOp_Join [ Card=0 ] LogOp_Get TBL: Northwind.dbo.products(alias TBL: p) Northwind.dbo.products TableID=533576939 TableReferenceID=0 IsRow: COL: IsBaseRow1000 [ Card=0 ] LogOp_Get TBL: Northwind.dbo.categories(alias TBL: c) Northwind.dbo.categories TableID=309576141 TableReferenceID=0 IsRow: COL: IsBaseRow1001 [ Card=0 ] ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1) ScaOp_Logical x_lopAnd ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [p].CategoryID ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [p].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) AncOp_PrjList ******************* *** Simplified Tree: *** LogOp_Join [ Card=0 ] LogOp_Select [ Card=0 ] LogOp_Get TBL: Northwind.dbo.products(alias TBL: p) Northwind.dbo.products TableID=533576939 TableReferenceID=0 IsRow: COL: IsBaseRow1000 [ Card=0 ] ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [p].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) LogOp_Select [ Card=0 ] LogOp_Get TBL: Northwind.dbo.categories(alias TBL: c) Northwind.dbo.categories TableID=309576141 TableReferenceID=0 IsRow: COL: IsBaseRow1001 [ Card=0 ] ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Identifier QCOL: [p].CategoryID ******************* Begin selectivity computation Input tree: LogOp_Select CStCollBaseTable(ID=1, CARD=77 TBL: Northwind.dbo.products AS TBL: p) ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [p].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) Plan for computation: CSelCalcColumnInInterval Column: QCOL: [p].CategoryID Loaded histogram for column QCOL: [p].CategoryID from stats with id 2 Selectivity: 0.480519 Stats collection generated: CStCollFilter(ID=3, CARD=37) CStCollBaseTable(ID=1, CARD=77 TBL: Northwind.dbo.products AS TBL: p) End selectivity computation Begin selectivity computation Input tree: LogOp_Select CStCollBaseTable(ID=2, CARD=8 TBL: Northwind.dbo.categories AS TBL: c) ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) Plan for computation: CSelCalcColumnInInterval Column: QCOL: [c].CategoryID Loaded histogram for column QCOL: [c].CategoryID from stats with id 1 Selectivity: 0.375 Stats collection generated: CStCollFilter(ID=4, CARD=3) CStCollBaseTable(ID=2, CARD=8 TBL: Northwind.dbo.categories AS TBL: c) End selectivity computation Begin selectivity computation Input tree: LogOp_Join CStCollFilter(ID=3, CARD=37) CStCollBaseTable(ID=1, CARD=77 TBL: Northwind.dbo.products AS TBL: p) CStCollFilter(ID=4, CARD=3) CStCollBaseTable(ID=2, CARD=8 TBL: Northwind.dbo.categories AS TBL: c) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Identifier QCOL: [p].CategoryID Plan for computation: CSelCalcExpressionComparedToExpression( QCOL: [p].CategoryID x_cmpEq QCOL: [c].CategoryID ) Selectivity: 0.333333 Stats collection generated: CStCollJoin(ID=5, CARD=37 x_jtInner) CStCollFilter(ID=3, CARD=37) CStCollBaseTable(ID=1, CARD=77 TBL: Northwind.dbo.products AS TBL: p) CStCollFilter(ID=4, CARD=3) CStCollBaseTable(ID=2, CARD=8 TBL: Northwind.dbo.categories AS TBL: c) End selectivity computation *** Join-collapsed Tree: *** LogOp_Join [ Card=37 ] LogOp_Select [ Card=37 ] LogOp_Get TBL: Northwind.dbo.products(alias TBL: p) Northwind.dbo.products TableID=533576939 TableReferenceID=0 IsRow: COL: IsBaseRow1000 [ Card=77 ] ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [p].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) LogOp_Select [ Card=3 ] LogOp_Get TBL: Northwind.dbo.categories(alias TBL: c) Northwind.dbo.categories TableID=309576141 TableReferenceID=0 IsRow: COL: IsBaseRow1001 [ Card=8 ] ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Identifier QCOL: [p].CategoryID ******************* *** Tree Before Project Normalization *** LogOp_Join [ Card=37 ] LogOp_Select [ Card=37 ] LogOp_Get TBL: Northwind.dbo.products(alias TBL: p) Northwind.dbo.products TableID=533576939 TableReferenceID=0 IsRow: COL: IsBaseRow1000 [ Card=77 ] ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [p].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) LogOp_Select [ Card=3 ] LogOp_Get TBL: Northwind.dbo.categories(alias TBL: c) Northwind.dbo.categories TableID=309576141 TableReferenceID=0 IsRow: COL: IsBaseRow1001 [ Card=8 ] ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Identifier QCOL: [p].CategoryID ***************************************** *** Tree After Project Normalization *** LogOp_Join [ Card=37 ] LogOp_Select [ Card=37 ] LogOp_Get TBL: Northwind.dbo.products(alias TBL: p) Northwind.dbo.products TableID=533576939 TableReferenceID=0 IsRow: COL: IsBaseRow1000 [ Card=77 ] ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [p].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) LogOp_Select [ Card=3 ] LogOp_Get TBL: Northwind.dbo.categories(alias TBL: c) Northwind.dbo.categories TableID=309576141 TableReferenceID=0 IsRow: COL: IsBaseRow1001 [ Card=8 ] ScaOp_Comp x_cmpLt ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [c].CategoryID ScaOp_Identifier QCOL: [p].CategoryID **************************************** --------------------------------------------------- Apply Rule: JoinCommute - A JOIN B -> B JOIN A Begin selectivity computation Input tree: LogOp_Join CStCollBaseTable(ID=2, CARD=8 TBL: Northwind.dbo.categories AS TBL: c) CStCollFilter(ID=3, CARD=37) CStCollBaseTable(ID=1, CARD=77 TBL: Northwind.dbo.products AS TBL: p) ScaOp_Comp x_cmpEq ScaOp_Identifier QCOL: [p].CategoryID ScaOp_Identifier QCOL: [c].CategoryID Plan for computation: CSelCalcExpressionComparedToExpression( QCOL: [c].CategoryID x_cmpEq QCOL: [p].CategoryID ) Selectivity: 0.125 Stats collection generated: CStCollJoin(ID=8, CARD=37 x_jtInner) CStCollBaseTable(ID=2, CARD=8 TBL: Northwind.dbo.categories AS TBL: c) CStCollFilter(ID=3, CARD=37) CStCollBaseTable(ID=1, CARD=77 TBL: Northwind.dbo.products AS TBL: p) End selectivity computation --------------------------------------------------- Apply Rule: WCJNonSELtoIdxLookup - JN/LSJ/LASJ/LOJ on SEL -> IDX LOOKUP Estimating distinct count in utility function Input stats collection: CStCollFilter(ID=3, CARD=37) CStCollBaseTable(ID=1, CARD=77 TBL: Northwind.dbo.products AS TBL: p) Columns to distinct on:QCOL: [p].CategoryID Plan for computation: CDVCPlanFilter Overriding 1 columns Using ambient cardinality 37 to combine distinct counts: 3 Combined distinct count: 3 Result of computation: 3 --------------------------------------------------- Apply Rule: SelResToFilter - SelRes -> Filter --------------------------------------------------- Apply Rule: SelIdxToRng - SelIdx -> Rng --------------------------------------------------- Apply Rule: SelToIdxStrategy - Sel Table -> Index expression --------------------------------------------------- Apply Rule: GetIdxToRng - GetIdx -> Rng --------------------------------------------------- Apply Rule: SelectToFilter - Select()->Filter() --------------------------------------------------- Apply Rule: GetToScan - Get()->Scan() --------------------------------------------------- Apply Rule: GetToIdxScan - Get -> IdxScan --------------------------------------------------- Apply Rule: JNtoHS - JN -> HS Estimating distinct count in utility function Input stats collection: CStCollFilter(ID=4, CARD=3) CStCollBaseTable(ID=2, CARD=8 TBL: Northwind.dbo.categories AS TBL: c) Columns to distinct on:QCOL: [c].CategoryID Plan for computation: CDVCPlanUniqueKey Result of computation: 3 --------------------------------------------------- Apply Rule: JNtoSM - JN -> SM --------------------------------------------------- Apply Rule: SelectToFilter - Select()->Filter() --------------------------------------------------- Apply Rule: GetToIdxScan - Get -> IdxScan --------------------------------------------------- Apply Rule: EnforceSort - x0->Sort(x0) --------------------------------------------------- Apply Rule: EnforceSort - x0->Sort(x0) --------------------------------------------------- Apply Rule: JNtoSM - JN -> SM --------------------------------------------------- Apply Rule: SelToIdxStrategy - Sel Table -> Index expression *** Stop search, level 1 ***
Next Steps
- You can create and compile this simple procedure in your application (or master) database and use it as a simple T-SQL tool for getting a detailed report on the internal cardinality estimator function call tree plus other statistics like cardinality, selectivity, applied rules, join rules, index operations, etc.
- The procedure works for SQL Server 2014 and later.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips