42

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?

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
Jeff Atwood
  • 12,994
  • 20
  • 74
  • 92
  • Take a look - [Glenn Berry - Eight Different Ways to Clear the SQL Server Plan Cache](https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/) – LCJ Jan 21 '19 at 22:51

4 Answers4

49

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;
Jeff Atwood
  • 12,994
  • 20
  • 74
  • 92
  • 2
    the advice to use a plan hint stands none the less. – Remus Rusanu Dec 05 '09 at 06: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 01:22
7

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.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
  • 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 https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-2017#execution-plan-caching-and-reuse – 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
1

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'
CodeCowboyOrg
  • 119
  • 3
0

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

sofa
  • 1
  • 1