How do I best measure the query performance? I have 2 stored procedures, where the second stored procedure is an improvement of the first one.
I'm trying to measure by exactly how much that is an improvement.

Measuring clock time doesn't seem to be an option as I get different execution times. Even worse, sometimes (rarely, but it happens) the execution time of the second stored procedure is bigger than the execution time of the first procedure (I guess due to the server workload at that moment).

Include client statistics also provides different results.

DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE are good, but the same story...

SET STATISTICS IO ON could be an option, but how could I get an overall score as I have many tables involved in my stored procedures?

Include actual execution plan could be an option also. I get an estimated subtreecost of 0.3253 for the first stored procedure, and 0.3079 for the second one. Can I say the second stored procedure is 6% faster (=0.3253/0.3079) ?

Using &quot;Reads&quot; field from SQL Server Profiler?


So how can I say that the second stored procedure is x% faster than the first procedure, no matter the execution conditions (the workload of the server, the server where these stored procedures are executed, etc)?
If it is not possible, how can I prove the second stored procedure has a better execution time than the first stored procedure?
I like to use the SQLQueryStress free tool when comparing a before and after scenario. With SQLQueryStress you can execute each stored procedure as many times as you like, and get the total average stats for all executions.

For example, you could execute each stored procedure 100 times, and then use the stats to back up your improvements. "Over 100 executions, my improvements save a total of 30 seconds and the stored proc does 1500 less reads per execution." I think you get the idea.

If there are parameters in the stored proc, it's always a good idea to double check that your improvements work with many different sets of parameters. SQLQueryStress does some cool stuff with letting you substitute parameters in your query to get a better overall picture of how the stored proc might be performing.

SQLQueryStress documentation: http://www.datamanipulation.net/sqlquerystress/documentation/documentation.asp