sql-server - 如何清除 SQL Server 查询缓存？

Question

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

SELECT * 
FROM Table 
WHERE Col = 'someval'

我第一次执行查询可能需要 > 15 secs 。后续执行返回 < 1 sec .

如何让 SQL Server 2005 不使用任何缓存结果？我尝试过运行

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

但这似乎对查询速度没有影响(仍然是 < 1 sec )。

Answer 1

这里有一些很好的解释。看看吧。

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

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO

来自链接的文章:

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.