0

enter image description hereDoes rpc_completed duration (micro_sec) = rpc_starting.timestamp - rpc_completed.timestamp? In my case, I saw a big difference between them. Duration is only few millsec, where rpc_starting.timestamp - rpc_completed.timestamp is 6 - 10 sec. enter image description here

I am on enterprise 2016 sp2-cu14, the duration field is in microsecond. I did similar thing for catching sp_statement_starting and sp_statement_completed, sp_batch_starting/sp_batch_completed inside rpc_starting and rpc_completed. it shows the same situation. My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"

2
  • 1
    What version of SQL Server, and what tool are you using to examine the events? Dec 5, 2020 at 22:28
  • 1
    The duration should be accurate. Can you test with SSMS to see which one is correct? Dec 6, 2020 at 15:07

2 Answers 2

-1

I don't believe the duration column represents the difference in time of the timestamp_UTC column between the rpc_starting and rpc_completed events. Assuming you're on any version of SQL Server still relevant (2008 R2+) that column is in units of microseconds (anything older version and it's in milliseconds), and therefore is a very small value in your examples (e.g. 1,872 microseconds = 0.00187 seconds).

My guess based on Microsoft's description is that is literally how long it took that particular event to occur and be recorded.

3
  • I am on enterprise 2016 sp2-cu14, the duration field is in microsecond. I did similar thing for catching sp_statement_starting and sp_statement_completed, sp_batch_starting/sp_batch_completed inside rpc_starting and rpc_completed. it shows the same situation. My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"
    – Andrew An
    Dec 5, 2020 at 23:53
  • From my understanding in the doc, it's the difference between the StartTime and EndTime field.
    – J.D.
    Dec 6, 2020 at 0:17
  • 1
    It is strange that many people only use duration in *_completed instead of difference between start and completed to determining long running queries. (e.g. duration>1000000 for more than 1 sec). In my case the duration is so short compare to start and completed.
    – Andrew An
    Dec 6, 2020 at 0:36
2

My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"

"Duration" in the sp_statement_completed event is how long the query actually took at the server. It doesn't include the time spent sending results to the client (usually, see here for details).

I'm not 100% sure why the Extended Events timestamp column shows such a large discrepancy between the "starting" and "completed" events, but that's not where you want to focus your attention.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.