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
456=‘All density’*card=0.003759399*121317
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
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
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.
Recent Comments