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