清空sql缓存

--有时候我们在对存储过程做性能测试的时候，需要清除SQLServer的缓存，以保证测试的有效性和科学性，
--这时候我们就需要使用到一个未公开的DBCC命令 DBCC FLUSHPROCINDB，如何使用这个命令，我们下面来介绍一下：
-- 这个命令的语法为：DBCC FLUSHPROCINDB(@DBID) [WITH NO_INFOMSGS],其中@DBID参数表示需要清除
--的过程所在的数据库的ID号，可通过查询sys.databases视图或者使用DB_NAME()函数来获取，
--WITH NO_INFOMSGS表示不返回执行后的系统报告信息。
-- 那么我们又如何知道SQLServer是否缓存了某个存储过程对象，这里我们会使用到一个动态视图
--sys.dm_exec_cached_plans，它会显示被缓存了的对象，其中包括执行计划，存储过程，表等等，这个在
--联机帮助有详细的说明，这里就不说明了，结合上面说的内容，我们使用一个例子来说明具体的使用方法。
-- 第一步：先创建一个测试数据库Test,然后执行以下代码
USE Test
GO
--查询被缓存了的存储过程
SELECT
AS object_name,
B.text AS sql_text
FROM sys.dm_exec_cached_plans A
CROSS APPLY sys.dm_exec_sql_text(A.plan_handle) B
INNER JOIN sys.procedures C
ON B.objectid = C.object_id
WHERE A.objtype = 'Proc'
-- 执行完以后，你会发现没有记录显示
--
-- 第2步：接着创建2个存储过程，并执行这2个存储过程
CREATE PROC P_Test1
AS
SET NOCOUNT ON
SELECT 1 AS ID
GO
CREATE PROC P_Test2
AS
SET NOCOUNT ON
SELECT 2 AS ID
GO
EXEC P_Test1
EXEC P_Test2
GO

--
-- 第3步：执行第1步中的代码，你会看到如下结果：
--
--
--
-- object_name sql_text
--
-----------------------------------------------------
--
-- P_Test2 CREATE PROC P_Test2.....
--
-- P_Test1 CREATE PROC P_Test1.....
--
--
--
-- 这时候我们可以看到SQLServer已经把这两个存储过程缓存起来了，
--
--
--
-- 第4步：清除缓存



SELECT DB_ID('Test') AS DB_ID --先找到Test的数据库ID，我的环境查出来是8

GO



DBCC FLUSHPROCINDB(8) WITH NO_INFOMSGS --清除过程缓存

GO



第5步：查看结果，同样执行第一步中的代码，你会再次看不到任何结果，说明缓存已经清除，很可惜的是

不能对某个存储过程进行缓存清除，只能清除数据库中所有的存储过程。因此我们可以写个小脚本来执行这个功能，如果我们有需要的时候，直接运行一下就好了，脚本如下：



USE master

GO



DECLARE

@DBName varchar(30),

@SchemaName varchar(30),

@ProcName varchar(30)



SELECT

@DBName = 'Test', --清除的数据库

@SchemaName = 'dbo', --存储过程的Schema

@ProcName = 'P_Test1' --存储过程名



EXEC('

USE ' + @DBName + '



DECLARE @DB

ID int



IF EXISTS(

SELECT

1

FROM sys.dm_exec_cached_plans A

CROSS APPLY sys.dm_exec_sql_text(A.plan_handle) B

INNER JOIN sys.procedures C

ON B.objectid = C.object_id

AND = ''' + @ProcName + '''

INNER JOIN sys.schemas D

ON C.schema_id = D.schema_id

AND = ''' + @SchemaName + '''

WHERE A.objtype = ''Proc'')

BEGIN

SELECT @DBID = DB_ID('''+ @DBName +''')



DBCC FLUSHPROCINDB(@DBID) WITH NO_INFOMSGS

END

')



以上脚本判断只有当该存储过程被缓存，才执行清除缓存语句，同时考虑了存储过程名一样，但Schema不

一样的情况。希望这篇文章对你有所帮助，也欢迎你提出更好的解决方案:)