Performance Tuning and Cache

When tuning queries and indexes for performance, it's a good idea to clear the cache when running benchmarks.
For example, when you first run a query, a plan is created, and then data is fetched from the drive.
On the next execute, your query plan won't need to be created, and the results will probably come from the cache, giving a false indication of how long the query will run.

So to clear the cache, execute these two statements:

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Then you can test with each execution being treated equally.

Take care on the FreeProcCache, as this is a server level command.   Do not run this on a production box unless you really know what you are doing!

peace