How to clear cache of 1 stored procedure in sql server

Question

I am using SQL Server 2008 R2.

I know that DBCC FREEPROCCACHE will clear cache of all stored procedures in SQL Server.

But what I need is to clear cache of only 1 stored procedure. How can I do that?

The Stored Procedure name is Rpt_RegionReport. I don't want to execute the stored procedure with WITH RECOMPILE option.

I am testing this stored proc only and don't want to disturb other stored procs cache — srh, Jun 02 '15 at 13:27

score 21 · Accepted Answer · answered Jun 02 '15 at 13:26

21

DBCC FreeProcCache has a single optional argument - the ID of the execution plan you want to delete.

You can find the plan you want to delete using sys.dm_exec_cached_plans, and then you can just use it as

DBCC FREEPROCCACHE (0x0123456....);

answered Jun 02 '15 at 13:26

Luaan

62,244
7
97
116

10

awesome. I get the plan_handle with this query: `select cp.plan_handle from sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st where OBJECT_NAME(st.objectid, st.dbid) = 'Rpt_RegionReport'` then store it in a variable and pass it to `DBCC FREEPROCCACHE` – srh Jun 02 '15 at 14:21

score 10 · Answer 2 · answered Sep 05 '19 at 19:41

Here is another way to delete the plan from the cache only for a stored procedure:

DECLARE @PlanHandle VARBINARY(64);

SELECT  @PlanHandle = deps.plan_handle
FROM    sys.dm_exec_procedure_stats AS deps
WHERE   deps.object_id = OBJECT_ID('dbo.SomeProcedureName') AND deps.database_id = DB_ID();

IF @PlanHandle IS NOT NULL
    BEGIN
        DBCC FREEPROCCACHE(@PlanHandle);
    END
GO

score 6 · Answer 3 · edited Feb 03 '20 at 20:42

6

Wouldn't executing sp_recompile on the stored procedure work?

EXEC sp_recompile N'SP_Name';

edited Feb 03 '20 at 20:42

MarredCheese

17,541
8
92
91

answered Oct 21 '19 at 19:49

Sandhya

606
1
7
13

score 4 · Answer 4 · answered Jun 02 '15 at 13:29

4

just find the plan using this query and clean the plan_handle

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

DBCCFREEPROCCACHE(0x0600010069AB592540C10089000000000000000000000000)

Plan_handle

answered Jun 02 '15 at 13:29

mohan111

8,633
4
28
55

4

I don't think you should have `cp.objtype = N'Adhoc' AND cp.usecounts = 1` as it might filter out the plan in question. You shouldn't assume that someone doing testing is hitting the proc from SSMS; the user could be hitting the proc from app code or a testing framework. And `usecounts` can "be incremented multiple times when using showplan", according to the MSDN page for [sys.dm_exec_cached_plans](https://msdn.microsoft.com/en-us/library/ms187404.aspx). – Solomon Rutzky Jun 02 '15 at 13:40

vibs2006 · Answer 5 · 2019-05-09T06:38:03.587

The Query given by @mohan might have some issues as it is filtering only objtype as Adhoc which means all other queries excluding procedure will show up here.

So Please Execute the following set of statements to selectively clear only selected procedure's execution plan.

--Enter Name of your Procedure. I have used here as 'SP_PrepareCustomers'. You should replace it with the name of your procedure. 
DECLARE @NameOfProcedure VARCHAR(255) = 'SP_PrepareCustomers'

DECLARE @planHandle VARBINARY(64) = (SELECT top 1 plan_handle
   FROM   sys.dm_exec_cached_plans AS cp
          CROSS APPLY sys.dm_exec_sql_text(plan_handle)
   WHERE  cp.cacheobjtype = N'Compiled Plan'
          AND cp.objtype = N'Proc'
          AND cp.usecounts = 1
          AND TEXT LIKE '%' + @NameOfProcedure + '%')

IF @planHandle IS NOT NULL
  BEGIN     
      PRINT 'Procedure with name like ' + @NameOfProcedure + ' plan handle found with value as given below:'
      PRINT @planHandle   
      DBCC FREEPROCCACHE (@planHandle)
      PRINT 'Execution plan cleared for the procedure'
  END
ELSE
  BEGIN
      PRINT 'No Plan was found for the selected procedure '
            + @NameOfProcedure
  END

How to clear cache of 1 stored procedure in sql server

5 Answers5

Linked