Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 13 down vote accepted

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:

share|improve this answer
the advice to use a plan hint stands none the less. – Remus Rusanu Dec 5 '09 at 6:49
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 2 days ago

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):

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.

share|improve this answer
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

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' 
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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