Jun 04 2012

How to Drop One Plan from Cache

While presenting this weekend at SQL Saturday #117 in Columbus, OH (great event, if you missed it, you missed it), I had what I thought was a little piece of throw-away code, but several people from the audience asked about it. Here it is:

DBCC FREEPROCCACHE(0x05000700618F532C40E190CE000000000000000000000000) ;

Not much to it is there?

The trick is, starting with SQL Server 2008, you can use the FREEPROCCACHE command to drop a single plan from the cache rather than completely clearing out the cache. I use it to show compile times & bad parameter sniffing and other things. You can use it to get rid of a plan in cache for whatever you might need to do that. You certainly don’t need to drop the entire procedure cache as people so frequently do. The only trick to using this is that you need to get the plan handle, that long, meaningless string inside the parentheses above. You can do that using this query (or several others):

SELECT  decp.plan_handle
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
WHERE   dest.[text] LIKE 'CREATE PROC dbo.spAddressByCity%';

I’m joining the plans from cache that are displayed through sys.dm_exec_cached_plans to the query text through sys.dm_exec_sql_text and, in this case, searching for the CREATE PROCEDURE statement to find the one I’m interested in. That’s a quick & dirty way to get the job done. Simple stuff, but hopefully helpful.

8 Comments

  • By Benjamin Nevarez, June 4, 2012 @ 10:18 pm

    You can also use sp_recompile :-)

    Ben

  • By Grant Fritchey, June 5, 2012 @ 6:33 am

    Yep, that’ll sure do it too. Thanks Ben.

  • By Jonlee, June 7, 2012 @ 4:30 am

    Nice script.. Using it now but excluding ‘%SELECT decp.plan_handle%’.

    Thanks!

  • By Henrik Staun Poulsen, June 7, 2012 @ 8:01 am

    You could also use OPTION (RECOMPILE) if you’re using SQL Server 2008 R2 and above

  • By Grant Fritchey, June 7, 2012 @ 8:42 am

    You can use RECOMPILE. But only if you want to modify the query. If you want to just yank a plan out of cache, this is the quick & easy way to do it. The thing to remember with any query that has RECOMPILE built in, it just won’t get stored in cache. You’ll get a new plan every time, but nothing gets stored. But you’re right, it’s one way to get a plan out of cache.

  • By Tim Harkin, December 11, 2012 @ 11:10 am

    Used this today, worked like a charm.

  • By Grant Fritchey, December 11, 2012 @ 12:10 pm

    Good to know Tom. Thanks!

Other Links to this Post

  1. Throwback Thursday #4: Plan Cache | JohnSterrett.com — January 23, 2014 @ 1:22 pm

RSS feed for comments on this post. TrackBack URI

Leave a comment