Severe database query latency due to bad query plan reuse

Question

Severe database query latency due to bad query plan reuse

Emma Brooks 0

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

1 answer

Your answer

Answer 1

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.