Parameter Sniffing

Small vehicle are usually used to deliver pizza orders; determine by the route and order quantity. Lets make an assumption, whereby the staff are not alert that he/she could deliver with large vehicle. He/she might as well making multiple round trips to complete large order.

A wise way was to deliver the order with a large vehicle like van.

Same goes with SQL Server, SQL server compile (create and cached execution plan) stored procedure during the first execution, Do take note that SQL Server compile the stored procedure, TOGETHER with the parameter value. Future stored procedure execution will resued the cached execution plan, as written in MSDN:

When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the Database Engine.

Using the example by Greg Larsen.

When the first execution of stored procedure are filtering small date range, the stored procedure are using index seek, and as you can see, it is fast!

Table 'BillingInfo'. Scan count 1, logical reads 2673, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

However, after the first caching occur, whenever the stored procedure are filtering big date range, it will still used the same cached execution plan, which are not efficient.

Table 'BillingInfo'. Scan count 1, logical reads 334751, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now lets turn everything upside down, when the first execution of stored procedure are filtering big date range, the stored procedure will be using clustered index scan.

Regardless no matter what is the date range after the first execution, all execution of stored procedure will remain using the same execution plan. And as you predict, below statement will be identical no matter what the date range is.

Table 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Conclusion
Different parameter during the first execution of stored procedure gonna effect how the stored procedure work and performance in the future.

Solution?
  1. include syntax WITH RECOMPILE,

CREATE PROC [schema name].[stored procedure name] (@name) WITH RECOMPILE

This specific syntax will alert SQL Server to remove the stored procedure cached execution plan and rebuilt new cached execution plan. You could assume that every execution of the stored procedure will trigger drop procedure and create procedure.
Pros (Every recompile produce the optimized execution plan),
Cons (Not suitable for frequent fire stored procedure, creating new execution plan will affect performance).

  2. Cheat SQL Server about parameter, Create new variable and assign it with parameter.
Cons (You may need to edit yours stored procedure).

 3. Nested Stored Procedure. Execute different stored procedure with the date range condition.
Cons (Hard to maintain lots of stored procedure).

  4. Include Table hints.
Cons (You really need to know what you are doing, this method may required you to change the hits from time to time).


Reference
Parameter Sniffing by Greg Larsen on 20 Sept 2010
https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

The Elephant and the Mouse, or, Parameter Sniffing in SQL Server by Jes Schultz Borland 26 Jun 2013
http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Image Source
http://www.kharkovinfo.com/food-delivery.html
http://yogurberryatl.com/new-delivery-online-contest/
https://www.iconfinder.com/icons/61629/home_house_icon
http://www.psdgraphics.com/tag/black/page/4/