SQL Server Performance Forum – Threads Archive

sql 2005 profiler

i have a saved trace file thats been loaded into a table
– i am concerned about one of the sp’s in that trace table so my trace ran for 1 hour the total time for that one sp equals 290 minutes ?!?!?!?!? trace data is stored in microseconds in 2005
– so i do a select ((sum(duration)/1000)/1000)/60 from dbo.TestCasePID90 where objectname = ‘sptIndividualAddUpdateFull’ that equals 290 minutes. how can this be? my trace ran only for 60 minutes.

The procedures may have executed simulataniously! Roji. P. Thomas
http://toponewithties.blogspot.com

please explain that cause when i look at profiler i assume these are ALL unique calls

if one sp can call others sp, at the end you can have the total time from first sp who call others.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

so sp:completed reports what exactly with nested sp calls and triggers? this is my exact case: sptIndividualAddUpdateFull is the main stored proceure. Inside it there are 4 nested stored procedure calls. Create Procedure dbo.sptIndividualAddUpdateFull
as
… exec dbo.sptIndividualDetailsUpdate @ …
exec dbo.sptIndividualAddressUpdate @ …
exec dbo.sptIndividualContactUpdate @ …
exec dbo.sptIndividualListingUpdate @ …
note: also there is a trigger in the Individual table and other tables. These triggers fire when these stored procedures run. so when i look at SP:Completed, Duration in SQL 2005 profiler for sptIndividualAdUpdateFull, this duration time should be the total time for that sp to complete right?

In my oppinion you are right.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

ya. but cannot figure out why this total time is off. ran a trace for 60 minutes total time for all the sptIndividualAddUpdateFull = 290 minutes
(sp:completed..duration)

further details… select sum(duration) from dbo.TestCasePID90 where objectname = ‘sptIndividualAddUpdateFull’ –17439795518 micro
–17439795.518 mili
–17439.795 secs
–290 mins
again – this trace only ran for aprox. 60 minutes select min(endtime) from TestCasePID90 –2006-10-12 21:01:59.680
select max(endtime) from TestCasePID90 –2006-10-12 22:11:10.760

has to be simulataniously processing here. if my trace had start time data i would be able to confirm. (<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> cheers

]]>