Clear cache for a single Stored Procedure

Sometimes for testing purposes (or an emergency) one might find it useful to remove query plan for a single stored procedure. This way you won't have to disrupt the entire instance (and possibly anger your fellow developers!).

I often forget that DBCC FREEPROCCACHE accepts a plan handle as a parameter value. There are a few ways to identify the plan handle, usually it involves the sys.dm_exec_cached_plans with a cross apply on sys.dm_exec_sql_text and sys.dm_exec_query_plan. (Feel free to scour the internet for a fully written query, I'm not going to reinvent the wheel here!).

Once you identify the proc and plan handle that you want to remove from cache simply run:

DBCC FREEPROCCACHE('0x0500050082DC5313608CD1C25C0000000100000000000000000000')

See the plan handle placed in parenthesis. This gives you granular control over the procedure cache.

Why not use Recompile? Of course this is an option and a lot more readable. Depending on your needs you can simply run

Exec sp _Recompile N'ProcName'

There are so many more options involved with procedure cache and recompilation. Feel free to check out MS documentation FreeProcCache and Recompile.

#PerformanceTuning #DMVs #TSQL #sqlserver