Parameter Sniffing

Parameter sniffing with repro steps:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/parameter-sniffing/


Other good articles explaining parameter sniffing:

https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

https://hackernoon.com/why-parameter-sniffing-hurts-your-sql-query-performance-d73c0da71fbc


I encountered that problem some time in 2014, if I remember correctly I was able to solve that problem using this:

DROP PROC [dbo].[DisplayBillingInfo]
GO
CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
WITH RECOMPILE
AS 
DECLARE @StartDate DATETIME;
DECLARE @StopDate DATETIME;
SET @StartDate = @BeginDate;
SET @StopDate = @EndDate;
SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @StartDate AND @StopDate; 

Basically, it prevents SQL Server from sniffing the parameter since it can't identify the parameter values used in the SELECT statement as the WHERE clause is not directly using the parameter variable that passed to it, thereby making SQL Server create a different execution plan every time the stored procedure is executed instead of reusing old execution plan that could otherwise be non-optimal for a given set of parameter values.