sql-server - 有人可以解释一下 DBCC DROPCLEANBUFFERS 吗？

Question

有人可以解释一下 DBCC DROPCLEANBUFFERS 与 CHECKPOINT 运算符的使用并提供示例吗？

我知道在查询之前测试运行的性能很有用，但我没有完全掌握它。另外，我不知道与 CHECKPOINT 运算符结合使用。

如果我在查询之前执行它，它们需要更长的时间来运行，所以我猜测比较性能是件好事，但不能准确评估它们。

如有任何帮助，我们将不胜感激!

Answer 1

CHECKPOINT将修改后的数据页刷新到磁盘:

[CHECKPOINT] Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

DBCC DROPCLEANBUFFERS然后删除所有非脏(iow，干净)页面:

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

最终结果是您一开始没有缓存任何内容。