2

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?

1 Answer 1

2

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.

2
  • 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? Commented Feb 7, 2013 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. Commented Feb 8, 2013 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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