Best procedure to measure query optimization effects? I could not find valuable answers or explanations to the following question:

How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps. 

Currently I do the following:


restore the production DB to an isolated test server, to cut off external influence / usage that falsifies results by additional server usage.
rebuild all indexes and statistics.


Then I repetitively execute the following steps:


SET STATISTICS IO ON - to get information about IO usage
DBCC FREEPROCCACHE; CHECKPOINT; - to free plan cache and consider e.g. index changes when repeating / testing the changes
DBCC DROPCLEANBUFFERS; - to avoid usage of cached data to make sure I have a fair comparison on each query execution
run the query


After each execution I quickly import all the statistics outputs into an excel sheet, to compare the changes to originals (see picture) as well as the runtime, checking if the number of returned rows still match the original and inspect the new execution plan to see the effects.

Unfortunately this process takes a lot of time because each execution with cleared caches takes about 15 minutes to complete. 


Is this procedure fine or should I consider changes?
Is clearing out the data cache really necessary or is it still a fair comparison to execute the query with the data cache since there is no other users active that could make changes to that cache?
At the risk of stating the obvious, first decide what you are optimizing for. Almost always it is elapsed time, though you may be trying to reduce CPU use (to reduce license fees) or IO (to avoid a disk upgrade, say). Then find a way to measure that thing, holding other variables constant. Be sure your chosen measure actually measures what you target and not a bunch of other stuff too.

For example, running a query in SSMS and looking at the status bar execution time will show round-trip network time plus plan compilation time plus IO time plus query execution time plus client caching time plus SSMS rendering time. For large result sets and slow networks the overhead and variability may swamp any actual improvement.

Having a completely clean bufferpool and plan cache for each itteration will give great reproducability. It is unlikely to be the actual production scenario, however. Since production is what you're trying to make fast then I'd suggest you test in a production-like environment, with similar memory &amp; disk, plans compiled and the working set in the bufferpool.