How to: Clear the cache for a stored procedure in SQL Server

OPTION 1 – Simple Approach


SELECT plan_handle, st.text

FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
where text like '%usp_StoredProcedureName%'

--from the results of the query copy and paste the value in the plan_handle column into the below query

dbcc FREEPROCCACHE(0x05000A00EB673A7CC01E434B0B00000001000000000000000000000000000000000000000000000000000000)

Option 2 – The Configurable and Automated Approach

 

DECLARE queryText_cursor CURSOR FOR
SELECT * FROM (VALUES 
	('%usp_StoredProcedureName1%'), 
	('%usp_StoredProcedureName2%')) as X(a);

OPEN queryText_cursor

DECLARE @queryText varchar(max)
FETCH NEXT FROM queryText_cursor 
INTO @queryText

WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE handle_cursor CURSOR FOR
	SELECT plan_handle--, text
	FROM sys.dm_exec_cached_plans 
	CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
	WHERE text LIKE @queryText;

	OPEN handle_cursor

	DECLARE @Handle varbinary(max)
	FETCH NEXT FROM handle_cursor 
	INTO @Handle

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @query nvarchar(max)=  N'DBCC FREEPROCCACHE (' + CONVERT(varchar(max),@Handle,1) + ');' 
		EXECUTE sp_executesql @query

		FETCH NEXT FROM handle_cursor 
		INTO @Handle
	END 
	CLOSE handle_cursor;
	DEALLOCATE handle_cursor;

	FETCH NEXT FROM queryText_cursor 
	INTO @queryText
END 
CLOSE queryText_cursor;
DEALLOCATE queryText_cursor;


 

Advertisements