• Facebook - Black Circle
  • Twitter Round
  • Instagram Black Round
  • LinkedIn - Black Circle

SEARCH BY TAGS: 

Please reload

RECENT POSTS: 

FOLLOW ME:

  • Facebook - Black Circle
  • Twitter - Black Circle
  • Instagram - Black Circle
  • LinkedIn - Black Circle

Clear cache for a single Stored Procedure

January 9, 2018

 

 

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

 

 

 

 

Share on Facebook
Share on Twitter
Please reload