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.
Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.
- MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019
