Home / server / Questions / 91285
Accepted
Jeff Atwood
Asked: 2009-12-05 22:16:30 +0800 CST

How do I remove a specific bad plan from the SQL Server query cache?

  • 772

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?

sql-server

4 Answers

  1. Best Answer

    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;
    • 49

  2. 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.

    • 7

  3. 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'
    • 1

  4. to find the right hash just look into the execution plan save that plan to xml or open it as xml,

    If you do not know where to find the execution plan: you can activate that plan in SSMS In the xml of the execution plan search for

    QueryHash="

    the hash is behind that text and somwhere behind the hash you can see RetrievedFromCache="true" if thats true you can find it in the sys.dm_exec_query_stats

    select  query_hash, sql_handle from sys.dm_exec_query_stats qs where convert(varchar(255),query_hash,1) = '[your found hash goes here]'

    remark its important to convert because the hash is binary!!! now you got the sql_handle, time for final RUN

    DBCC FREEPROCCACHE ([the sql_handle goes in here])

    and the plan is gone now its upto the engine to create a new one or with some bad luck you get the old one back. some (risky) tricks on that: -rewrite the query -update statistics -rebuild or reorganise indexes -include columns in an index -change the maxdop setting -run the first query with a representative set of parameters (warming up the server with the right exercises

    • 0