如何在SQL Server中清除一个存储过程的缓存

Question

我正在使用SQL Server 2008 R2。

我知道DBCC FREEPROCCACHE会清除SQL Server中所有存储过程的缓存。

但是我需要清除仅1个存储过程的缓存。如何做到这一点？

存储过程名称为Rpt_RegionReport。我不想使用WITH RECOMPILE选项执行存储过程。

- srh

---

为什么？你试图解决什么问题？ - Luaan

我只是在测试这个存储过程，不想干扰其他存储过程的缓存。 - srh

Answer 1

DBCC FreeProcCache有一个可选参数——你想要删除的执行计划的ID。

你可以使用sys.dm_exec_cached_plans查找要删除的计划，然后只需将其用作：

DBCC FREEPROCCACHE (0x0123456....);

- Luaan

---

太棒了。我可以通过这个查询获取plan_handle：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'，然后将其存储在一个变量中，并传递给DBCC FREEPROCCACHE。 - srh

Answer 2

以下是另一种仅针对存储过程从缓存中删除计划的方法：

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

- Vivek Vemula

Answer 3

执行存储过程上的sp_recompile不会起作用吗？

EXEC sp_recompile N'SP_Name'; 

- Sandhya

Answer 4

只需使用此查询查找计划并清除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)

计划处理

- mohan111

---

我认为你不应该使用 cp.objtype = N'Adhoc' AND cp.usecounts = 1，因为这可能会过滤掉相关的计划。你不能假设测试人员只从 SSMS 运行存储过程；用户也可能从应用程序代码或测试框架中运行它。而根据sys.dm_exec_cached_plans页面所述，当使用 showplan 时，usecounts 可能会被多次增加。 - Solomon Rutzky

Answer 5

@mohan提供的查询可能存在问题，因为它只过滤了objtype为Adhoc的内容，这意味着所有其他查询（除了存储过程）都会显示在这里。

因此，请执行以下一组语句，仅选择性地清除所选存储过程的执行计划。

--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 

- vibs2006