有时候我们在对存储过程做性能测试的时候,需要清除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不 一样的情况。希望这篇文章对你有所帮助,也欢迎你提出更好的解决方案:)
使用DBCC命令清除过程缓存
最新推荐文章于 2020-07-24 12:26:04 发布