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.
You can also use sp_recompile 🙂
Ben
Yep, that’ll sure do it too. Thanks Ben.
Nice script.. Using it now but excluding ‘%SELECT decp.plan_handle%’.
Thanks!
You could also use OPTION (RECOMPILE) if you’re using SQL Server 2008 R2 and above
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.
Used this today, worked like a charm.
Good to know Tom. Thanks!
[…] How to Drop One Plan from Cache – A lot of DBA’s think that DBCC FREEPROCCACHE is used only to drop all the execution plans from your instance. Grant Fritchey does a great job showing how you can utilize this DBCC command to remove only a single plan. […]
Does anyone have a script which will automatically detect and delete the bad plan instead of first searching the plan handle and then delete it ?
You have to be able to define what constitutes a “bad” plan here. A regression in performance? If you’re on SQL Server 2017 or better this can be done automatically. Otherwise, you need to have some mechanism of measuring the performance and then based on that performance remove a plan, which will cause a recompile, but, you won’t necessarily get the plan you want in the recompile. Here’s info on the plan regression in 2017 (and Azure SQL Database): https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/05/17/automatic-plan-correction-in-sql-server-2017/