phil.doensen (1/23/2014)
It's query and data caching that will speed this up the second and third time round.I find
DBCC DROPCLEANBUFFERS
will probably get you back to the 70sec mark,
but to get to the original 2 minutes you need to restart the SQL Server.
Very necessary when you are actually trying to work out if your
query optimisations are actually any better or you have just wasted
the last two hours re-writing a script for no result.
You don't need to restart the SQL Server to get a "clean" instance for testing.
CHECKPOINT will flush all "dirty" pages (pages that have been modified but not yet written to disk) to disk, resulting in a "clean" buffer cache.
Then, DBCC DROPCLEANBUFFERS will drop all the buffers. Now, you have no data pages in memory, so no queries can take advantage of physical reads performed by previous queries.
Finally, DBCC FREEPROCCACHE will drop cached execution plans (either all of them or specified ones) so that every query has to compile a new plan and no query can re-use an already compiled plan.
Other than that, the only other variable to manage is contention, which can really only be avoided by ensuring that no other processes are running on the server at the time.
Jason Wolfkill