Hi All,

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:
Declare @start datetime
Select @Start = Getdate()
exec sp_DailyRun_old '2004-02-18'
Select Datediff(ms, @start, getdate()) as 'runtime(ms)'
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.

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?

Thanks again,