如何清除 SQL Server 查询缓存?

2021-12-20 00:00:00 caching sql-server

我有一个针对 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 sec).

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.

相关文章