Is this Parameter Sniffing?

Question

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.

Thanks

Answer 1

If the query plan really doesn't change then it's not a parameter sniffing issue. Some more information is needed to diagnose this issue though. Maybe there is a subtle difference in the query plans that is not immediately obvious. If the query has scalar functions, these execute in their own context with their own associated query plans. We can only make guesses at this point though.

What would be useful would be to capture what the query is doing at various points in its execution. Community scripts like sp_whoisactive, and sp_BlitzWho are useful here. You can see the statement currently executing. The current wait type and session level wait information are useful for diagnosing the problem.

Also, consider a monitoring tool – I created an open-source tool DBA Dash. This will automatically capture the running query information (similar to sp_whoisactive or sp_BlitzWho). If you had the tool running, you could go back in time and work out exactly why your query was slow 6 days ago. It will help if the problem re-occurs while you are not around to troubleshoot.