Someone asks how to invalidate the SQL Server procedure cache. It is tempting to remember one DBCC command and stop there. The more useful answer is to ask how much cache really needs to be affected.
Question: How do you invalidate a cached procedure plan?
Answer: DBCC FREEPROCCACHE without an argument clears the plan cache broadly. That is what my original answer described, and its production warning remains important. Clearing cached plans forces later executions to compile again. At a busy time, many simultaneous compilations can add CPU pressure and latency.
If one stored procedure is the problem, diagnose that procedure first. sys.sp_recompile can mark the named stored procedure so its plan is compiled on its next execution. A particular cached plan can be removed with DBCC FREEPROCCACHE (plan_handle) after you have verified that handle. A query can also use OPTION (RECOMPILE) when statement-level recompilation is truly intended. These choices have different scopes and costs.
DBCC FREESYSTEMCACHE and DBCC FREESESSIONCACHE, which I mentioned in the old answer, address different caches: FREESYSTEMCACHE releases unused cache entries, while FREESESSIONCACHE flushes the distributed-query connection cache. They are not interchangeable ways to fix a single stored procedure. A full cache clear can also erase evidence of the plan you wanted to study. Save the plan and performance information before changing cache state.
The interview point is simple: know the broad command, explain its risk, and choose the narrowest action supported by a diagnosis. A fresh plan is not a substitute for fixing poor estimates, indexing, or query design.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
2 Comments. Leave new
Are you saying that in some way removing a procedure’s plan from the cache executes the stored procedure ?
Are you saying that removing a procedure’s plan from cache executes the stored procedure ? That doesn’t seem reasonable.