如何在sql server中清除1个存储过程的缓存

I am using SQL Server 2008 R2.

我正在使用SQL Server 2008 R2。

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

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

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

但我需要的是清除只有1个存储过程的缓存。我怎样才能做到这一点?

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

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

Thanks

2 个解决方案

#1

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

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

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

您可以使用sys.dm_exec_cached_plans找到要删除的计划,然后您可以将其用作

DBCC FREEPROCCACHE (0x0123456....);

#2

just find the plan using this query and clean the plan_handle

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

#1