Remove individual plan from cache

* Why would I need to do that? – When you find a query hogging your system and suspects that it just have gotten hold of a bad query plan. You need it to recompile a new plan. But just recompile for that query, for all other queries you want them to keep their plans, you don’t want to kill the cache completely. * Who can do that? – You of course, at least after reading this article and following it, but you also need to have gotten some permissions: At least “VIEW SERVER STATE” to be able to see DMV to find plan handle and “ALTER SERVER STATE” to affect the cache. * Where, i.e in which versions can I do this? – SQL Server 2008 and later * OK! How do I do it then?

– Find spid and then kill plan with dbcc freeproccache (plan_handle), so it will get recompiled the next execution time, an example of the process outlined below:

1) First you need to get hold of which SPID is running the bad plan (probably did that already, since you have found a query you want to “attack”, anyways use “sp_who2 active” to find it) SP_WHO2 active –say you find that spid = 111 is the villain.

2) Then you need to get the plan cache handle of the bothering query: --find plan handle, and some other interesting stuff DECLARE @spid INT = 111 -- Change 111 to the SPID that is hogging the system SELECT sp.spid , sp.blocked AS Blocker , DB_NAME(sp.dbid) AS DBName , RTRIM(sp.nt_username) AS UserName , er.status AS Status , er.wait_type AS WaitType , SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) AS Query , CASE WHEN UPPER(qt.text) LIKE '%CREATE PROC%' THEN SUBSTRING(qt.text, CHARINDEX('CREATE PROC', UPPER(qt.text)), 100) ELSE LEFT(LTRIM(qt.text), 100) END AS ParentQuery , sp.program_name AS ProgramName , sp.hostname , RTRIM(sp.nt_domain) AS nt_domain , er.start_time , qp.query_plan , er.plan_handle FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) as qp WHERE er.session_id = @spid ORDER BY 1, 2 3) After finding the right query, and its plan handle, copy the result in the last column “plan_handle” to exchange in the following command and execute it to clear that execution plan from memory DBCC FREEPROCCACHE ( < plan_handle > ) (for example: DBCC FREEPROCCACHE (0x06002D0095E6600540E17EF6080000000000000000000000) )

You should get a message something similar to this: “DBCC execution completed. If DBCC printed error messages, contact your system administrator.” The other users will not be affected. The query will recompile at next execution, hopefully finding the right plan this time. Otherwise you might find yourself in need of an SQL Server Consultant to help you pin down what is really the problem.

/Jonas Bergström