使用DBCC命令清除过程缓存

  有时候我们在对存储过程做性能测试的时候，需要清除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 

      C.name 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 @DBID 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 C.name = ''' + @ProcName + '''

  INNER JOIN sys.schemas D

   ON C.schema_id = D.schema_id

    AND D.name = ''' + @SchemaName + ''' 

  WHERE A.objtype = ''Proc'')

 BEGIN

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



  DBCC FLUSHPROCINDB(@DBID) WITH NO_INFOMSGS

 END

')



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

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