Microsoft documentation doesn’t usually dwell on dangers of executing admin commands. It is somewhat correctly assumed that if you have the access to run it, you know what you’re doing, but DBCC FREEPROCCACHE is one of those commands that even Books OnLine (BOL) urges extra caution. “Use DBCC FREEPROCCACHE to clear the plan cache carefully.” – it says. In fact, most people probably use it only on test environments when writing a blog post about query plans. If, however, you know what you’re doing, there is a clever way to use it with a pinpoint accuracy. While it is not advisable to clear your entire proc cache, it may be often useful to get rid of a plan for one object. Observe:
First we get the query plan handle by SP name:
DECLARE @plan_handle varbinary(64)=(
select top 1
qs.plan_handle
from sys.dm_exec_query_stats (nolock) qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
where 1=1
AND qt.dbid=db_id('mydb')
AND qt.objectid=object_id('MySP')
)
with this info in hand we can clear just this plan:
DBCC FREEPROCCACHE(@plan_handle)
note that I’m getting only one query plan handle at a time, so if this object has more than one version of a query plan, you’d have to run the command again.
Enjoy, and as always, don’t forget to write with feedback.
