Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 at 13:32

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

    
Well to start with you can run both procedures at once and look at execution plan. See which procedure gives you better execution plan and has lower cost. Note: Cost is not always correlates to better or worse performance. –  Vladimir Oselsky Mar 11 at 13:38
    
@VladimirOselsky: Execution plans are big enough, so it isn't that obvious to get the winner. –  Mihai Bejenariu Mar 11 at 13:42
1  
What about adding manual tracking to the procedure. In the beginning of the procedure add DECLARE @StartTime Datetime2 = SYSDATETIME(); and than at the end do another one DECLARE @EndTime Datetime2 = SYSDATETIME() and insert those values into some table for review. Once your procedure have been executed for some many times review AVG,MAX,MIN difference in seconds, milliseconds or even nanoseconds. –  Vladimir Oselsky Mar 11 at 13:51
    
@VladimirOselsky: Already did that (see first option in my post), but I get different execution times. E.g. if I executed first stored procedures 10 times, I get even 5 seconds difference on global execution time... –  Mihai Bejenariu Mar 11 at 13:55
1  
I'm not sure you can say that a stored procedure is better by 15% than a previous version, when the load on the system is not the same when you test. Your best bet would be to test on a dev/test server and do a real load test (using your own scripts or SQLQueryStress). I would measure IO, CPU, plan cost - all on cold and warm runs, then I'd see where are the improvements (not all of them are equal). –  Marian Mar 12 at 7:29

3 Answers 3

up vote 6 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

SQLQueryStress

share|improve this answer
    
+1. Anyway how this tool handles the case when you have over 100 executions and just in the middle a heavy operation is performed on the server?! –  Mihai Bejenariu Mar 11 at 14:43
    
What about caching?! –  Mihai Bejenariu Mar 11 at 14:47
1  
Do you have a dev/test or lab server that you could restore the database to in order to test the stored procedures without any interference from the production workload? It's probably always a safer option to do any testing on a different server than production. What are your concerns with caching? –  SQL_JGood Mar 11 at 14:54
    
I'm afraid data is read from cache as I'm executing the same SP with the same parameters each time, which is not the real behaviour in PROD where the parameters are different and the cached data is changed. –  Mihai Bejenariu Mar 11 at 15:07
3  
That's where I would look into using SQLQueryStress to simulate different parameters. Check out the documentation link and read up on the tool. You can essentially select data from a column from a table and use each different value from that column as a parameter to pass into the stored proc. link –  SQL_JGood Mar 11 at 15:14

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 at 13:59
    
+1 Great tool, reading those stats is usually a pain. –  Vladimir Oselsky Mar 11 at 14:00
    
@Nelson G: can measure how much optimized SP2 over SP1? –  Mihai Bejenariu Mar 11 at 14:05

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
    
Where did you get that 6%? –  Mihai Bejenariu Mar 11 at 14:10
    
The idea is I want to have a valid answer before delivering the second stored procedure to PRODuction. How do I know the second stored procedure is more optimized? –  Mihai Bejenariu Mar 11 at 14:15
1  
@MihaiBejenariu the 6% is mentioned in the question. –  ypercube Mar 11 at 14:16
1  
@ypercube: that 6% mentioned at option 5 is the real way of computing the difference? Do you mean i should compare "estimated subtreecost"? If yes, do you have any reference about? –  Mihai Bejenariu Mar 11 at 14:34
    
No, I don't really mean that. I just pointed to your question and where it mentions 6%. I don't think sub-tree costs should be taken as exact calculations but I am really not the right person to answer this. –  ypercube Mar 11 at 14:41

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.