SQL Trace Events SP:Completed and SP:StmtCompleted

Question

Hi,

I have a stored procedure that has only one INSERT statement in it. I have been profiling out the application and I see that Sp:Completed take much longer than Sp:StatCompleted. Here are the records in the profiler for this store procedure

  Event             SPID    Duration    Start Time                                    EndTime                              Event Sequence

SP:Starting       89                  2012-04-17 16:46:19.907                                                            23646649
SP:StmtStarting   89                  2012-04-17 16:46:19.907                                                            23646652
SP:StmtCompleted  89      0           2012-04-17 16:46:19.907                       2012-04-17 16:46:19.907              23646657
SP:Completed      89      791         2012-04-17 16:46:19.907                       2012-04-17 16:46:20.700              23652337 

As you can see the statement completes really fast but the stored procedure takes 700 ms to complete. I am really confused as to what could be causing this. What could the stored procedure can be doing after the statement completes?

Your help is really appreciated

Answer 1

Hi tadar,

The duration of SP:Completed is the time to complete execution of a whole stored procedure, while the duration of SP:StmtCompleted is the time to execute a SQL statement executed as part of a stored procedure.

Generally speaking, the duration of SP:Completed can be larger than sum of SP:StmtCompleteds’. It will include the time for evaluation (such as triggers constraints for data integrity), determining whether to recompile the execution plan, and so on. To eliminate unnecessary processes to complete the stored procedure, please pay attention to this blog: My stored procedure "best practices" checklist.

---

Stephanie Lv

TechNet Community Support

Answer 2

Hi Stephanie,

thanks for your reply. The interesting issue is that most of the issues that you mentioned happens before the statement is run. in this case the statement start time is the same as the stored procedure and completes very quickly. However, store procedure then was waiting for something else to complete. after more profiling i saw that the only event that was occurring, that might have contributing to this, was writing to transaction log. after moving the transaction log to a different drive most of the delays went away and now the application is much more stable.

Thanks again for your help.