Severe database query latency due to bad query plan reuse

Question

Severe database query latency due to bad query plan reuse

Emma Brooks 20

Hello,

Within our Windows enterprise environment, we manage our core transactional operations via our database services. For the past few days, execution times have suddenly surged from mere milliseconds to several minutes, which is severely hindering our daily business workflows.

The query engine appears to be caching and reusing inefficient execution plans, and overall system performance is suffering significantly as a result. How can we cleanly clear cached query plans for specific stored procedures without purging the entire server plan cache?

What can I do?

Thank you!

0 comments

Answer accepted by question author

Tracy Le 14,655 Independent Advisor

Hello Emma Brooks,

If you want to invalidate the cached plan for a single stored procedure without affecting the rest of the instance, EXEC sp_recompile N'dbo.YourProcedure' is usually the safest option. This marks the procedure for recompilation, and SQL Server generates a new execution plan the next time it runs.

If an immediate plan removal is required, you can identify the procedure's plan_handle from sys.dm_exec_procedure_stats and use DBCC FREEPROCCACHE(plan_handle). That removes only the targeted plan from cache and does not clear plans belonging to other workloads.

Since execution times increased from milliseconds to minutes, I would also investigate parameter sniffing, statistics age, and recent plan regressions. Updating statistics and reviewing Query Store for a previously good plan are often more effective long-term solutions than repeatedly clearing the cache. If Query Store is enabled, forcing a known good plan can help stabilize performance while the root cause is being investigated.

If this information helps, please consider accepting the answer.

Tracy Le.

Emma Brooks 20 Reputation points

2026-10-25T17:41:10.55+00:00

Hi Tracy, thank you so much for the detailed explanation, running sp_recompile immediately resolved the execution delay and our transaction times are back to normal.

Since this seems related to parameter sniffing as you mentioned, would you recommend forcing a good plan via Query Store permanently or is optimizing the query with OPTIMIZE FOR a better long-term approach????

1 additional answer

Your answer

Emma Brooks 20 Reputation points

2026-10-25T17:41:10.55+00:00

Hi Tracy, thank you so much for the detailed explanation, running sp_recompile immediately resolved the execution delay and our transaction times are back to normal.

Since this seems related to parameter sniffing as you mentioned, would you recommend forcing a good plan via Query Store permanently or is optimizing the query with OPTIMIZE FOR a better long-term approach????

Answer 1

Hello Emma Brooks,

Glad sp_recompile got things back to normal. I’d treat forcing a plan in Query Store as a stabilizer rather than a permanent fix. It requires no code change, but the forced plan can become a poor choice as data volumes shift, and forcing can fail silently. If you keep it, check force_failure_count and last_force_failure_reason_desc in sys.query_store_plan regularly.

For the long term, the right choice depends on your data. OPTIMIZE FOR @param = value works when one value truly represents most calls. OPTIMIZE FOR UNKNOWN gives a stable plan based on average density, though rarely the best one. If the data is heavily skewed and the procedure doesn’t run thousands of times per second, OPTION (RECOMPILE) on the problem statement is often the most reliable. On SQL Server 2022 with compatibility level 160, Parameter Sensitive Plan optimization caches multiple plans automatically. You can also apply these hints through sys.sp_query_store_set_hints without changing code.

Which SQL Server version are you on? Thanks for accepting the answer.