Just a general question:
只是一个普遍的问题：
Is there a query/command I can pass to SQL Server not to use cache when executing a particularly query?
是否有一个查询/命令我可以传递给SQL Server在执行特殊查询时不使用缓存？
I am looking for a query/command that I can set rather than a configuration setting. Is there no need to do this?
我正在寻找一个可以设置的查询/命令而不是配置设置。有没有必要这样做？
3 个解决方案
#1
21
DBCC FREEPROCCACHE
Will remove all cached procedures execution plans. This would cause all subsequent procedure calls to be recompiled.
将删除所有缓存的程序执行计划。这将导致重新编译所有后续过程调用。
Adding WITH RECOMPILE to a procedure definition would cause the procedure to be recompiled every time it was called.
将WITH RECOMPILE添加到过程定义将导致每次调用过程时重新编译过程。
I do not believe that (in SQL 2005 or earlier) there is any way to clear the procedrue cache of a single procedures execution plan, and I'd doubt you could do it in 2008 either.
我不相信（在SQL 2005或更早版本中）有任何方法可以清除单个程序执行计划的程序缓存，我怀疑你也可以在2008年完成。
#2
16
If you want to force a query to not use the data cache, the best approach is to clear the cache before you run the query:
如果要强制查询不使用数据高速缓存，最好的方法是在运行查询之前清除高速缓存：
CHECKPOINT
DBCC DROPCLEANBUFFERS
Note that forcing a recompile will have no effect on the query's use of the data cache.
请注意，强制重新编译不会影响查询对数据高速缓存的使用。
One reason you can't just mark an individual query to avoid the cache is the cache use is an integral part of executing the query. If the data is already in cache, then what would SQL Server do with the data if it was read a second time? Not to mention sync issues, etc.
您不能只标记单个查询以避免缓存的一个原因是缓存使用是执行查询的一个组成部分。如果数据已经在缓存中，那么如果第二次读取数据，SQL Server会对数据做什么？更不用说同步问题等
#3
2
use
使用
WITH RECOMPILE
#1
21
DBCC FREEPROCCACHE
Will remove all cached procedures execution plans. This would cause all subsequent procedure calls to be recompiled.
将删除所有缓存的程序执行计划。这将导致重新编译所有后续过程调用。
Adding WITH RECOMPILE to a procedure definition would cause the procedure to be recompiled every time it was called.
将WITH RECOMPILE添加到过程定义将导致每次调用过程时重新编译过程。
I do not believe that (in SQL 2005 or earlier) there is any way to clear the procedrue cache of a single procedures execution plan, and I'd doubt you could do it in 2008 either.
我不相信（在SQL 2005或更早版本中）有任何方法可以清除单个程序执行计划的程序缓存，我怀疑你也可以在2008年完成。
#2
16
If you want to force a query to not use the data cache, the best approach is to clear the cache before you run the query:
如果要强制查询不使用数据高速缓存，最好的方法是在运行查询之前清除高速缓存：
CHECKPOINT
DBCC DROPCLEANBUFFERS
Note that forcing a recompile will have no effect on the query's use of the data cache.
请注意，强制重新编译不会影响查询对数据高速缓存的使用。
One reason you can't just mark an individual query to avoid the cache is the cache use is an integral part of executing the query. If the data is already in cache, then what would SQL Server do with the data if it was read a second time? Not to mention sync issues, etc.
您不能只标记单个查询以避免缓存的一个原因是缓存使用是执行查询的一个组成部分。如果数据已经在缓存中，那么如果第二次读取数据，SQL Server会对数据做什么？更不用说同步问题等
#3
2
use
使用
WITH RECOMPILE