I have a particular stored procedure that takes 1 parameter.
This proc is executed once a day with the same parameter value everyday. The row count is also the same with each run of the proc.
Day 1 - Runs for 10 minutes.
Day 2 - Runs for 8 hours.
Each run produces the exact same query plan.
In an attempt to remedy this, I added OPTION (RECOMPILE) to the problematic statement and it seems to have fixed it. It's been 5 days and each run has been 10 minutes.
My understanding of bad parameter sniffing is when the cached query plan is not optimal for certain parameter values.
So, how do I actually define this problem?
What would cause this drastic change in runtimes if the query plan is exactly the same and the parameter value is the same?
Edit: Query Store is enabled on the database. Using this, I was able to determine that the query plans are identical between good and bad runs.
This is the part of the query plan that does the bulk of the work.