I have a stored proc that takes 2 dates as parameters. The dates are used to specify a range of dates for the first in a series of CTEs to limit the scope.
When I run the query in SSMS as a query, it runs in 15 seconds and the query plan shows 81% on a sort.
When I make it a proc with the two date variables as parameters, it runs 19 MINUTES. 46% is on a clustered index scan.
Removing the parameters from the stored proc and adding them as variables inside the proc, it’s back to 15 seconds and 81% on a sort.
Can someone explain to me why this would be?
This might be a case of parameter sniffing.
You could try simply defining two local date variables at the top of your stored procedure, and assign them the value of the two input date parameters. Then replace the usage of the input parameters in the stored procedure code with those new local variables.
This has usually done the trick for me in the past, when dealing with parameter sniffing.