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 an existing stored procedure, and an updated version of that same stored procedure where changes have been made, but the same resultset is returned.

The updated version of the procedure focused on using existing #temp tables in order to do calculations as opposed to going back to the database a lot of times in order to get result values. Also a few tweaks were made to the updated version of the procedure.

Now the updated version runs a bit faster, but I can't consistently prove it because the line to the database which I query is remote and fluctuates with usage on the line.

My question - is there a common denominator which I can use as a "performance score", in order to see which of the 2 procedures are more efficient - less database calls for example?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

When you review/compare a SQL Server Execution Plan, each plan is given a cost value. Although these values can indeed be used for a relative comparison between plans, it is often more beneficial to dig a little deeper than that.

For example, you could compare the execution plans of the two stored procedures, perhaps using a tool such as the Free Plan Explorer from SQL Sentry.

This would allow you to break down the "score" you seek into more useful metrics, such as execution time, CPU consumption, Disk IO etc. and use these for your comparison.

Using this method would enable you to establish exactly where your performance gain(or loss) is achieved.

share|improve this answer
    
thanks. Would this be an option if I'm not allowed 3rd party tools: SET SHOWPLAN_TEXT OFF GO SET STATISTICS PROFILE ON GO and then tally up the TotalSubTreeCost? –  Peter PitLock Feb 7 '13 at 14:37
    
Using STATISTICS PROFILE, you only need reference the TotalSubtreeCost of the first row. It's cumulative of all the rows that follow. –  John Sansom Feb 8 '13 at 10:45
add comment

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.