DISABLE_PARAMETER_SNIFFING
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, and it has same effect as 'OPTIMIZE FOR UNKNOWN' , These three have exactly same effect. If you are not fa...