Tuesday, August 2, 2016

How to remove a specific execution plan from the SQL Server query cache

Below query will provide you the list of query plans available in the instance:

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 the output select the appropriate plan_handle based on query text. and run below query
 
DBCC FREEPROCCACHE (plan_handle_id_goes_here)