It's common knowledge that once a query is executed, the information

(data pages and execution plan) from the first query is retained in SQL

Server memory buffers. So if you run the exact same query again, your

speed and io statistics should be better than the first. This is

because SQL Server has compiled the execution plan and read the

necessary pages from disk and placed them in memory.

What if you are trying to troubleshoot a query that is poor on its

first attempt but then improves on subsequent calls to the same query?

In order to test a query and retest the same query under the same

conditions, consider using the following two DBCC commands, DBCC

DROPCLEANBUFFERS and DBCC FREEPROCCACHE.

* DBCC DROPCLEANBUFFERS removes the data from memory that was retrieved

from the first query.

* DBCC FREEPROCCACHE removes all of the query/execution plans from the

procedure cache.

By doing this after each repetitive attempt, you will ensure that your

io, time, and compile statistics are consistent between each query

test.