Post reply

Duration Column Exceeding Elapsed Time

  • March 23, 2010 at 7:35 pm

    #213098

    I'm helping with a performance tuning project at work and thought I understood the duration column in SQL Server Profiler, but am questioning myself now. I've been capturing profiles of small tests that we've been running, and comparing the time the application took (total elapsed time) to the time that it took SQL Server to finish (total duration time). For example, the application took 00:44:33 (44min, 33 seconds) to complete, and the sum of the duration column in SQL Profiler for that time period took 00:12:55. I thought I was on track until a co-worker gave me a profile from a much longer test. His elapsed time was about 6 hours, but the sum of the Profiler duration column was about 7 days. All Profiles were captured on the same SQL Server box.

    Anyone have any insight on this?

  • March 24, 2010 at 8:41 am

    #1138952

    The server is a dual Xeon quad-core machine with 12GB of RAM.

  • March 26, 2010 at 7:28 pm

    #1140856

    The SQL Server "Duration Time" probably is not including some or all of what is normally called the "transmission time", that is, the network+etc time that it takes to transmit the query results from the server back to the application client. With any kind of network & output buffering, it is entirely possible for the query to finish well before the application receives all of the data.

    There are a couple of other possibilities as well, but this is the SOP explanation.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 27, 2010 at 7:18 am

    #1140923

    I'm not sure I understand. It sounds like you're saying that it's normal for the SQL Duration time to be less than the elapsed time, and I agree. The problem is that it's just the opposite. The SQL Duration time is far greater than the elapsed time of the profile.

    I started another thread here , and ran another test with the same results.

    Thanks for your reply!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply