We have a overnight agent job and one of the steps runs particular slow recently. The step calls a stored procedure. I used sql server profiler to capture the trace for that proc into a table. I captured sp:StmtCompleted and sp:Completed events. And in column filter, I set ApplicationName to be like %step 6%, where step 6 is the step in question. And I set ObjectName to be like sp_xxx, where sp_xxx is the proc called by step 6.
I got the trace next morning and found the duration for sp:StmtCompleted is 1.87 hour (calculated), which is similar to the job history for that step. But when I sum up duration of all sp:StmtCompleted events, the total is about 8 seconds. I rerun the proc on a test environment with production data and got similar result (several seconds). I went through the text data of all sp:StmtCompleted events ordered by starttime and found it's a correct execution of all relevant statements in the proc.
I am wondering why the sum of duration of all sp:StmtCompleted events are not equal to that of sp:Completed ?
A side question: I already set ObjectName to be like sp_xxx in column filter but still got lot of records with objectname 'null'. What should I do?