Take the 2-minute tour ×
Server Fault is a question and answer site for professional 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
add comment

3 Answers

up vote 11 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
FROM 
   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:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;
share|improve this answer
1  
the advice to use a plan hint stands none the less. –  Remus Rusanu Dec 5 '09 at 6:49
add comment

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.

Update

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

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

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.

DECLARE @SQL NVARCHAR(4000)
SELECT @SQL = 'SELECT * FROM Table WHERE Column LIKE @NAME OPTION (RECOMPILE)'
EXEC sp_executesql @SQL, N'@NAME varchar(15)', 'MyName' 
share|improve this answer
add comment

Your Answer

 
discard

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.