如何清除SQL Server查询缓存？ [英] How can I clear the SQL Server query cache?

问题描述

我有一个针对SQL Server 2005运行的简单查询

I've got a simple query running against SQL Server 2005

SELECT * 
FROM Table 
WHERE Col = 'someval'

我第一次执行查询可以花费> 15秒。随后的执行返回到< 1秒。

The first time I execute the query can take > 15 secs. Subsequent executes are back in < 1 sec.

如何获取SQL Server 2005不使用任何缓存的结果？我已经尝试运行

How can I get SQL Server 2005 not to use any cached results? I've tried running

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

但这似乎对查询速度没有影响（仍然 <1秒）。

But this seems to have no effect on the query speed (still < 1 sec).

推荐答案

这里有一些很好的解释。

Here is some good explaination. check out it.

http： //www.mssqltips.com/tip.asp?tip=1360

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO

来自链接的文章：

如果所有性能测试都在SQL Server中进行，最好的方法可能是发出CHECKPOINT，然后发出DBCC DROPCLEANBUFFERS命令。尽管CHECKPOINT进程是SQL Server中的一个自动内部系统进程，并且会定期发生，但是发出此命令以将当前数据库的所有脏页写入磁盘并清理缓冲区很重要。然后可以执行DBCC DROPCLEANBUFFERS命令从缓冲池中删除所有缓冲区。

If all of the performance testing is conducted in SQL Server the best approach may be to issue a CHECKPOINT and then issue the DBCC DROPCLEANBUFFERS command. Although the CHECKPOINT process is an automatic internal system process in SQL Server and occurs on a regular basis, it is important to issue this command to write all of the dirty pages for the current database to disk and clean the buffers. Then the DBCC DROPCLEANBUFFERS command can be executed to remove all buffers from the buffer pool.

这篇关于如何清除SQL Server查询缓存？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！