Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from stackoverflow.com Mar 11 '14 at 13:32

This question came from our site for professional and enthusiast programmers.

up vote 9 down vote accepted

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

EDIT

Adam Mechanic allowed the domain hosting SQLQueryStress to expire, but has posted the source code for the tool here:

http://sqlblog.com/blogs/adam_machanic/archive/2016/01/04/sqlquerystress-the-source-code.aspx

SQLQueryStress

share|improve this answer

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

share|improve this answer
    
Logical reads: 3101 vs 2943. Read-Ahead reads: 0 vs 8. Scan count: 637 overall. Physical reads: 0 overall. LOB Logical Reads: 156 overall. – Mihai Bejenariu Mar 11 '14 at 13:59

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. The free tool does not let you compare versions, but the evaluation (and the pro) version does.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.