SQL - flushing the execution cache

Question

I am trying to profile several queries, but as you know, when you run the query a second time, it returns in 0 ms. I use

DBCC FREEPROCCACHE 

but that doesn't seem like a trick. what else can I run to clear any trace of the run / result cache?

Answer 1

 CHECKPOINT; DBCC dropcleanbuffers; 

However, this should not be done on the production server. CHECKPOINT is a data region command that will write dirty buffers to disk so that the following command DBCC dropcleanbuffers them, but DBCC dropcleanbuffers is global, and all data pages DBCC dropcleanbuffers from the buffer cache in this way should be read from disk when used in the next time.

Answer 2

You will also want to use DBCC DROPCLEANBUFFERS. This will check requests with a cold buffer cache.

 DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS