none
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





    • Edited by tadar Wednesday, April 18, 2012 12:16 AM
    Wednesday, April 18, 2012 12:08 AM

Answers

  • 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.

    • Marked as answer by Stephanie Lv Tuesday, April 24, 2012 12:22 AM
    Monday, April 23, 2012 11:40 PM

All replies

  • 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

    Thursday, April 19, 2012 6:07 AM
  • 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.

    • Marked as answer by Stephanie Lv Tuesday, April 24, 2012 12:22 AM
    Monday, April 23, 2012 11:40 PM