Can one assign a performance score to a stored proc to compare performance of 2 procs

Question

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?

Answer 1

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.