Menu Close

TSQL – Remove Query Cashe

Each query we execute has an execution plan that is cashed. Once cashed if we run the query again it will return the results quicker than the 1st time. In order to remove the procedure cashed we need to do the following:

This will remove all procedure cache and NOT just a specific one!

— Determine the id of the current database
— and flush the procedure cache for only that database
DECLARE @intDBID AS INT = (SELECT DB_ID());
DBCC FLUSHPROCINDB (@intDBID);

To find all plan handles in a db run the query below:

— Find the plan handle for a db
— OPTION (RECOMPILE) keeps this query from going into the plan cache
— st.text AS [Query]: your query
SELECT cp.plan_handle, cp.objtype, cp.usecounts,
DB_NAME(st.dbid) AS [DatabaseName]
, st.text AS [Query]

FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE DB_NAME(st.dbid) = ‘
OPTION (RECOMPILE);

To remove a specific plan from the cache using the plan handle:

DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

source