How to let SQL Server know not to use Cache in Queries?

Question

Hello all,

Just a general question:

Is there a query/command I can pass to SQL Server not to use cache when executing a particularly query?

I am looking for a query/command that I can set rather than a configuration setting. Is there no need to do this?

Answer 1

use

WITH RECOMPILE

Answer 2

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.

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.

Answer 3

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.