How do I best measure the query performance?

Question

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.

1. 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).

2. Include client statistics also provides different results.

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

4. 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?

5. 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) ?

6. Using "Reads" 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?

Answer 1

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: https://github.com/ErikEJ/SqlQueryStress/wiki

Answer 2

4/ You can go to https://statisticsparser.com/ and paste your stats in order to see the overall score.

Answer 3

When you have collected execution times over a couple of days for your two stored procedures, I will recommend that you use this homepage

http://www.evanmiller.org/ab-testing/t-test.html

to see if they are actually different.

6% difference does not sound as much, when it comes to improvements of stored procedures. I've come to expect two orders of magnitude from my colleague, and I pretend to be disappointed if he only achieves one order of magnitude...

He does not have to use the EvanMiller homepage to prove that his solution works faster.

I would also install SQLSentrys (edit:) Plan Explorer from http://www.sqlsentry.com/ as this is a much improved tool for comparing execution plans.