This article is contributed. See the original author and article here.

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———————————-


 


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.