Free Plan Cache for Stored Procedure

When creating a procedure, you may need to free the plan cache to create a new execution plan as the code changes. Or you may already be experiencing performance issues with a particular procedure and you would like to get a new execution plan based on the user queries.

The following procedure frees the plan cache for a particular procedure that you know has issues:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FREECACHEFORPROC]
    @DatabaseName NVARCHAR(128),
    @ProcedureName NVARCHAR(128)
AS
    SET NOCOUNT ON;
    DECLARE @plan_handle varbinary(64);
	
	select @plan_handle = plan_handle 
	from sys.dm_exec_procedure_stats
	where object_name(object_id, database_id) like '%' + @ProcedureName  + '%'
	and database_id = (select dbid FROM master.dbo.sysdatabases where name = @DatabaseName);
	
        /* free plan cache */
	DBCC FREEPROCCACHE (@plan_handle);
        
        /* force recompile */
	exec sp_recompile @ProcedureName;
GO

Example:

dbo.FREECACHEFORPROC @DatabaseName = 'Northwind', @ProcedureName = 'myProc';