Selectivity and Estimated Row: Variable

Published May 26 2021 07:29 PM 1,616 Views
Microsoft

SQL Server does not sniff for variable, it just simply uses the fixed value.

 

I'm going to use AdventureWorks 2019 in this post.

--------------------Please run this script---------------

use AdventureWorks2019

go

IF exists(select 1 from sys.tables where name='SalesOrderDetail' and schema_id=schema_id('dbo'))

      drop table SalesOrderDetail

go

select * into SalesOrderDetail from [Sales].[SalesOrderDetail]

go

create statistics iProductID ON SalesOrderDetail(productid) with fullscan

go

dbcc traceon(3604,2363)---trace flag 2363 displays more detail about the selectivity

go

--------------------Please run this script---------------

 

 

Equality(=): all density

DECLARE @pid INT = 0

SELECT * FROM SalesOrderDetail WHERE ProductID = @pid

Liwei_1-1622081895512.png

 

 

456='All density'*card=0.003759399*121317

Liwei_4-1622082278046.png

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

----------------------------------trace flag 2363 output----------------------------------

Begin selectivity computation

Input tree:

  LogOp_Select

      CStCollBaseTable(ID=1, CARD=121317 TBL: Sales.SalesOrderDetail)

      ScaOp_Comp x_cmpEq

          ScaOp_Identifier QCOL: [AdventureWorks2019].[Sales].[SalesOrderDetail].ProductID

          ScaOp_Identifier COL: @pid

Plan for computation:

  CSelCalcHistogramComparison(POINT PREDICATE)

Loaded histogram for column QCOL: [AdventureWorks2019].[Sales].[SalesOrderDetail].ProductID from stats with id 3

Selectivity: 0.0037594

Stats collection generated:

  CStCollFilter(ID=2, CARD=456.079)

      CStCollBaseTable(ID=1, CARD=121317 TBL: Sales.SalesOrderDetail)

End selectivity computation

----------------------------------trace flag 2363 output----------------------------------

 

 

Non-Equality(<>): 0.9

 

DECLARE @pid INT = 0

SELECT * FROM SalesOrderDetail WHERE ProductID <> @pid

Liwei_2-1622081895514.png

 

 121317*0.9=109185.3, is rounded down to 109185

 

----------------------------------trace flag 2363 output----------------------------------

Begin selectivity computation

Input tree:

  LogOp_Select

      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)

      ScaOp_Comp x_cmpNe

          ScaOp_Identifier QCOL: [AdventureWorks2019].[dbo].[SalesOrderDetail].ProductI

          ScaOp_Identifier COL: @productid

Plan for computation:

  CSelCalcFixedFilter (0.9)

Selectivity: 0.9

Stats collection generated:

  CStCollFilter(ID=2, CARD=109185)

      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)

End selectivity computation

----------------------------------trace flag 2363 output----------------------------------

 

 

 

Inequality(>,>=,<,<=):0.3

declare @productid int=0

select *From SalesOrderDetail where ProductID>@productid

Liwei_3-1622081895516.png

 

121317*0.3=36395.1, is around down to 36395

Please try >=,< and <=, they all use the same selectivity.

----------------------------------trace flag 2363 output----------------------------------

Begin selectivity computation

Input tree:

  LogOp_Select

      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)

      ScaOp_Comp x_cmpGt

          ScaOp_Identifier QCOL: [AdventureWorks2019].[dbo].[SalesOrderDetail].ProductID

          ScaOp_Identifier COL: @productid

Plan for computation:

  CSelCalcFixedFilter (0.3)

Selectivity: 0.3

Stats collection generated:

  CStCollFilter(ID=2, CARD=36395.1)

      CStCollBaseTable(ID=1, CARD=121317 TBL: SalesOrderDetail)

End selectivity computation

 

----------------------------------trace flag 2363 output----------------------------------

 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2389886%22%20slang%3D%22en-US%22%3ESelectivity%20and%20Estimated%20Row%3A%20Variable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389886%22%20slang%3D%22en-US%22%3E%3CP%3ESQL%20Server%20does%20not%20sniff%20for%20variable%2C%20it%20just%20simply%20uses%20the%20fixed%20value.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20going%20to%20use%20AdventureWorks%202019%20in%20this%20post.%3C%2FP%3E%0A%3CP%3E--------------------Please%20run%20this%20script---------------%3C%2FP%3E%0A%3CP%3Euse%20AdventureWorks2019%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3EIF%20exists(select%201%20from%20sys.tables%20where%20name%3D'SalesOrderDetail'%20and%20schema_id%3Dschema_id('dbo'))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20drop%20table%20SalesOrderDetail%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3Eselect%20*%20into%20SalesOrderDetail%20from%20%5BSales%5D.%5BSalesOrderDetail%5D%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3Ecreate%20statistics%20iProductID%20ON%20SalesOrderDetail(productid)%20with%20fullscan%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3Edbcc%20traceon(3604%2C2363)---trace%20flag%202363%20displays%20more%20detail%20about%20the%20selectivity%3C%2FP%3E%0A%3CP%3Ego%3C%2FP%3E%0A%3CP%3E--------------------Please%20run%20this%20script---------------%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EEquality(%3D)%3A%20all%20density%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EDECLARE%20%40pid%20INT%20%3D%200%3C%2FP%3E%0A%3CP%3ESELECT%20*%20FROM%20SalesOrderDetail%20WHERE%20ProductID%20%3D%20%40pid%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_1-1622081895512.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284193iCAB6C18067C33B0A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Liwei_1-1622081895512.png%22%20alt%3D%22Liwei_1-1622081895512.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E456%3D%3CSTRONG%3E'All%20density'*card%3C%2FSTRONG%3E%3D0.003759399*121317%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_4-1622082278046.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284200iED2303F6F8939F30%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_4-1622082278046.png%22%20alt%3D%22Liwei_4-1622082278046.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDBCC%20execution%20completed.%20If%20DBCC%20printed%20error%20messages%2C%20contact%20your%20system%20administrator.%3C%2FP%3E%0A%3CP%3E----------------------------------trace%20flag%202363%20output----------------------------------%3C%2FP%3E%0A%3CP%3EBegin%20selectivity%20computation%3C%2FP%3E%0A%3CP%3EInput%20tree%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20LogOp_Select%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D1%2C%20CARD%3D121317%20TBL%3A%20Sales.SalesOrderDetail)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Comp%20%3CSTRONG%3Ex_cmpEq%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Identifier%20QCOL%3A%20%5BAdventureWorks2019%5D.%5BSales%5D.%5BSalesOrderDetail%5D.ProductID%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Identifier%20COL%3A%20%40pid%3C%2FP%3E%0A%3CP%3EPlan%20for%20computation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20CSelCalcHistogramComparison(POINT%20PREDICATE)%3C%2FP%3E%0A%3CP%3ELoaded%20histogram%20for%20column%20QCOL%3A%20%5BAdventureWorks2019%5D.%5BSales%5D.%5BSalesOrderDetail%5D.ProductID%20from%20stats%20with%20id%203%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESelectivity%3A%200.0037594%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EStats%20collection%20generated%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20CStCollFilter(ID%3D2%2C%20CARD%3D%3CSTRONG%3E456.079%3C%2FSTRONG%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D1%2C%20CARD%3D121317%20TBL%3A%20Sales.SalesOrderDetail)%3C%2FP%3E%0A%3CP%3EEnd%20selectivity%20computation%3C%2FP%3E%0A%3CP%3E----------------------------------trace%20flag%202363%20output----------------------------------%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENon-Equality(%26lt%3B%26gt%3B)%3A%200.9%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDECLARE%20%40pid%20INT%20%3D%200%3C%2FP%3E%0A%3CP%3ESELECT%20*%20FROM%20SalesOrderDetail%20WHERE%20ProductID%20%26lt%3B%26gt%3B%20%40pid%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_2-1622081895514.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284195i58849F7DCFEDEA4C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_2-1622081895514.png%22%20alt%3D%22Liwei_2-1622081895514.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B121317*0.9%3D109185.3%2C%20is%20rounded%20down%20to%20109185%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E----------------------------------trace%20flag%202363%20output----------------------------------%3C%2FP%3E%0A%3CP%3EBegin%20selectivity%20computation%3C%2FP%3E%0A%3CP%3EInput%20tree%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3BLogOp_Select%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D1%2C%20CARD%3D121317%20TBL%3A%20SalesOrderDetail)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Comp%3CSTRONG%3E%20x_cmpNe%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Identifier%20QCOL%3A%20%5BAdventureWorks2019%5D.%5Bdbo%5D.%5BSalesOrderDetail%5D.ProductI%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Identifier%20COL%3A%20%40productid%3C%2FP%3E%0A%3CP%3EPlan%20for%20computation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20%3CSTRONG%3ECSelCalcFixedFilter%20(0.9)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ESelectivity%3A%200.9%3C%2FP%3E%0A%3CP%3EStats%20collection%20generated%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20CStCollFilter(ID%3D2%2C%20CARD%3D109185)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D1%2C%20CARD%3D121317%20TBL%3A%20SalesOrderDetail)%3C%2FP%3E%0A%3CP%3EEnd%20selectivity%20computation%3C%2FP%3E%0A%3CP%3E----------------------------------trace%20flag%202363%20output----------------------------------%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EInequality(%26gt%3B%2C%26gt%3B%3D%2C%26lt%3B%2C%26lt%3B%3D)%3A0.3%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3Edeclare%20%40productid%20int%3D0%3C%2FP%3E%0A%3CP%3Eselect%20*From%20SalesOrderDetail%20where%20ProductID%26gt%3B%40productid%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_3-1622081895516.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284196iBB179CD84B97AF8D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Liwei_3-1622081895516.png%22%20alt%3D%22Liwei_3-1622081895516.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E121317*0.3%3D36395.1%2C%20is%20around%20down%20to%26nbsp%3B36395%3C%2FP%3E%0A%3CP%3EPlease%20try%20%26gt%3B%3D%2C%26lt%3B%20and%20%26lt%3B%3D%2C%20they%20all%20use%20the%20same%20selectivity.%3C%2FP%3E%0A%3CP%3E----------------------------------trace%20flag%202363%20output----------------------------------%3C%2FP%3E%0A%3CP%3EBegin%20selectivity%20computation%3C%2FP%3E%0A%3CP%3EInput%20tree%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20LogOp_Select%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D1%2C%20CARD%3D121317%20TBL%3A%20SalesOrderDetail)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Comp%3CSTRONG%3E%20x_cmpGt%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Identifier%20QCOL%3A%20%5BAdventureWorks2019%5D.%5Bdbo%5D.%5BSalesOrderDetail%5D.ProductID%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScaOp_Identifier%20COL%3A%20%40productid%3C%2FP%3E%0A%3CP%3EPlan%20for%20computation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20CSelCalcFixedFilter%20(0.3)%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESelectivity%3A%200.3%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EStats%20collection%20generated%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20CStCollFilter(ID%3D2%2C%20CARD%3D36395.1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CStCollBaseTable(ID%3D1%2C%20CARD%3D121317%20TBL%3A%20SalesOrderDetail)%3C%2FP%3E%0A%3CP%3EEnd%20selectivity%20computation%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E----------------------------------trace%20flag%202363%20output----------------------------------%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2389886%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎May 26 2021 08:01 PM
Updated by: