SQL Server Internal Query Cardinality Estimator Information

By: Eli Leiba | Updated: 2018-09-27 | 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

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-09-27