Unanswered: Measuring relative performance of stored procedures?
I recently had to change a stored procedure (which is actually a control procedure that calls several underlying stored procedures to get the job done) and saved off the old one so as to be able to compare the performance of the old vs. the new one.
I tried to do it in Sql Analyzer, but found that the measurements and execution time displayed there was piecemeal, in that in order to get total execution time, I had to manually add up the component execution times for all the query operations that made up the high-level stored procedure.
In an attempt to get an easier higher-level metric for comparison, I used the following code around each of the calls:
This gave me total elapsed time, but then I found that the metrics were pretty much useless on a busy system, since the total elapsed time is definately NOT CPU time and/or resource consumption numbers that would allow a reasonable comparison between the performance of the old and the new.
Declare @start datetime
Select @Start = Getdate()
exec sp_DailyRun_old '2004-02-18'
Select Datediff(ms, @start, getdate()) as 'runtime(ms)'
Is there a trick to the SQL analyzer (or a different way to capture the numbers in my test/metric code) that will allow "total execution performance" measurements?
Non est ei similis.
I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them