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.