DBCC FREEPROCCACHE DANGER

DBCC FREEPROCCACHE – use with caution

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.

Share this post

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Reply

Your email address will not be published.

Are you #CuriousAboutData? This is a members’ only discussion forum for any CTO, Developer, DBA, Data Engineer/Scientist — or anyone who just wants to know more about using and managing data. Members enjoy open debate, ask/ answer questions, offer opinions, expertise, and support. If your curiosity has no limits, this group is for you!