Parameter sniffing

  Sometimes, optimizer can opt for different query plans for the same query whenever we try to access the data through parameterized procedures. Based on the distribution of the data across the table, we may see different plans for different values sent to these parameters. This is called parameter sniffing. The drawback of this sniffing is, optimizer will not use plans stored in the cache. And, using optimized plan every time can be an advantage.

For example, if we are accessing reports based on sales date, for few dates there can be very few sales, and for few dates there can be more sales. In this scenario, optimizer can opt for different query plans based on those sales dates.

We have different ways to avoid (if you want) sniffing such as recompile, query hinting etc. recompile option recompiles the batch every time which can be cpu intensive. And query hinting, we are telling the optimizer to use a particular plan which is optimized for a different value.

SELECT SalesOrderDetailID, OrderQty

FROM Sales.SalesOrderDetail

WHERE ProductID = @ProductID
OPTION (OPTIMIZE FOR (@ProductID=945));