We have one particular SQL Server 2008 query (not a stored proc, but the same SQL string -- executes every 5 minutes) that intermittently caches a very bad query plan.

This query normally runs in a few milliseconds, but with this bad query plan, it takes 30+ seconds.

How do I surgically remove just the one bad cached query plan from SQL Server 2008, without blowing away the entire query cache on the production database server?


I figured out a few things

select * from sys.dm_exec_query_stats

will show all the cached query plans. Unfortunately, no SQL text is shown there.

However, you can join the SQL text to the plans like so:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

From here it's pretty trivial to add a WHERE clause to find the SQL I know is in the query, and then I can execute:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

to remove each query plan from the query plan cache. Not exactly easy or convenient, but it appears to work..

edit: dumping the entire query cache will also work, and is less dangerous than it sounds, at least in my experience:

  • 2
    the advice to use a plan hint stands none the less. – Remus Rusanu Dec 5 '09 at 6:49
  • 2
    I found this after my query magically refreshed it's bad plan but I plan to test it next time. A plan hint doesn't help if the query suffers from 'optional-itis' - where it has many optional parameters and it has been optimised for one set, then run for a different set. There is no optimal plan that can be attached for this kind of query. There is an optimal plan for one set of parameters which is in turn awful for another set of parameters. – Nick.McDermaid Nov 24 '15 at 1:22

If you know how the good plan looks like, just use a plan hint.

You cannot remove a specific cache entry, but you can clean an entire cache pool with DBCC FREESYSTEMCACHE(cachename/poolname).

You can get the cache name of a bad query plan if you have the plan handle (from sys.dm_exec_requests.plan_handle for the session_id in trouble during execution, or from sys.dm_exec_query_stats post execution):

select ce.name
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce on cp.memory_object_address = ce.memory_object_address
where cp.plan_handle = @bad_plan

However all SQL plans have the name 'SQL Plans' which makes picking the right one for DBCC FREESYSTEMCACHE a ... difficult choice.


Nevermind, forgot about DBCC FREEPROCCACHE(plan_handle), yes that will work.

  • 1
    The ability to pass a plan_handle to DBCC FREEPROCCACHE is available in SQL Server 2008 and not in SQL Server 2005. – Mario Jan 15 '10 at 23:12
  • What does it mean if sys.dm_exec_cached_plans has no entry in it for the plan_handle from sys.dm_exec_requests? – Jonathan Gilbert Mar 11 '19 at 18:13
  • @JonathanGilbert it means the plan was not cached, or it was evicted from cache. See docs.microsoft.com/en-us/sql/relational-databases/… – Remus Rusanu Mar 12 '19 at 11:37
  • So just to confirm, even though I only just started running that query, and the query has no hint saying not to cache it, it can be uncached because SQL Server made the value decision to not cache it? It wouldn't be because it's still running, right? If it decides to cache the plan, then it'd be cached right from the point at which the query starts running? – Jonathan Gilbert Mar 12 '19 at 17:28

The FREEPROCCACHE solution is fine, but a more direct way of doing this is to use OPTION (RECOMPILE) on your SQL String (you mentioned it wasn't an SP), this tells the Engine its a Single Use plan, because likely you suspect there is Parameter Sniffing or your Statistics are drastically different from run to run and you suspect its a Bad Cached Plan issue.

EXEC sp_executesql @SQL, N'@NAME varchar(15)', 'MyName' 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.