Remove a Specific Plan from the Cache

Sometimes the cure for a bad plan is to evict it from the cache and let SQL Server recompile. This query lists every cached plan with its plan handle, query plan hash, SQL text (full and statement-level), use count, object type, cache type, referencing plan count and the XML plan graph - so you can find the exact plan handle you want. The commented DBCC FREEPROCCACHE at the bottom shows how to remove it once found.

SELECT
	[Plan].plan_handle AS [Plan Handle],
	QueryStats.query_plan_hash as [Query Plan Hash],

	QueryStats.[sql_handle] as [SQL Handle],
	QueryStats.query_hash as [Query Hash],
	[QueryText].[text] AS [Full Text],

    SUBSTRING(QueryText.text,QueryStats.statement_start_offset/2 +1, 
		(CASE WHEN QueryStats.statement_end_offset = -1 
		THEN LEN(CONVERT(nvarchar(max), QueryText.text)) * 2 
		ELSE QueryStats.statement_end_offset end -
			QueryStats.statement_start_offset
		)/2
	) AS [Partial Text],
              	
	[Plan].[usecounts] AS [Use Count],
	[Plan].objtype AS [Object Type],
	[Plan].cacheobjtype AS [Cache Type],
	[Plan].refcounts AS [Referencing Plans],
	QueryPlan.query_plan AS [Plan Graph]
FROM
	sys.dm_exec_cached_plans AS [Plan]
CROSS APPLY sys.dm_exec_sql_text([Plan].plan_handle) AS QueryText
CROSS APPLY sys.dm_exec_query_plan([Plan].plan_handle) as QueryPlan
INNER JOIN sys.dm_exec_query_stats AS QueryStats
	ON QueryStats.plan_handle = [Plan].plan_handle
WHERE
	[QueryText].[text] NOT LIKE '%dm_exec_cached_plans%' --We want to ignore our own query
	--AND [QueryText].[text] LIKE Replace(Replace('%YOUR_QUERY_HERE%', ']', '\\]'), '[', '\\[') escape '\\'
GO

-- Remove a specific plan from the cache.
--DBCC FREEPROCCACHE(xxxxxxxxxxxxxxxxxxxxxxxxxx);
--GO

--Obtain additional information for the plan cache.
--SELECT * FROM sys.dm_exec_plan_attributes(xxxxxxxxxxxxxxxxxxxxxxxxxx);
--SELECT * FROM sys.dm_exec_query_plan(xxxxxxxxxxxxxxxxxxxxxxxxxx)