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

According to MSDN article, ‘DISABLE_PARAMETER_SNIFFING’ instructs Query Optimizer to use average data distribution while compiling a query with one or more parameters. This instruction makes the query plan independent on the parameter value that was first used when the query was compiled. This hint name is equivalent to trace flag 4136 or Database Scoped Configuration setting PARAMETER_SNIFFING = OFF.


 


 


Looks like it’s a pretty good hint, However, it doesn’t means you can resolve all parameter sniffing issue by using this query hint.


 


Actually,  the sentence ‘Query Optimizer to use average data distribution while compiling a query with one or more parameters’ is not 100% correct. It really depends on what symbol you used in the where clause.


 


‘DISABLE_PARAMETER_SNIFFING’ is a replacement of variable, these two have exactly same effect. If you are not familiar with selectivity of variable, please review  my post Selectivity and Estimated Row: Variable – Microsoft Tech Community


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)


——————–Please run this script—————


 


 


 


For example, following two stored procedure returns exactly same Estimated rows 456.


 


create proc ptest1


@pid int


as


select * from SalesOrderDetail where productid>=@pid option(use hint(‘DISABLE_PARAMETER_SNIFFING’))


go


create proc ptest2


@pid int


as


declare @pid1 int =@pid


select * from SalesOrderDetail where productid=@pid1


Go


Liwei_0-1622085421395.png


 


 


 


 


Trace flag 2363 displays more detail about the selectivity.


Liwei_1-1622085421402.png


 


 


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


 121317*0.0037594=456


 


 


Please review Selectivity and Estimated Row: Variable – Microsoft Tech Community for other inequations.

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