18. dubna 2012 0:08
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 procedureEvent 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
- Upravený tadar 18. dubna 2012 0:16
19. dubna 2012 6:07ModerátorHi 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.
TechNet Community Support
23. dubna 2012 23:40
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.
- Označen jako odpověď Stephanie LvModerator 24. dubna 2012 0:22