Duration per application as captured within a TRACE

  • Hi all,

    I just wonder why I see so much difference between durations within capureded trace-data??

    I'd like to know how many time each application has spend during the time the trace was captured.

    When I use the next script I see a big differances in values;

    SELECT SUM(CPU) as cpu,--Amount of CPU time (in milliseconds) used by the event.

    SUM(duration) as duration,--Amount of time (in milliseconds) taken by the event.

    SUM(

    CAST(DATEPART ( minute , endtime - starttime )*60000 +

    DATEPART ( second , endtime - starttime )*1000+

    DATEPART ( millisecond , endtime - starttime ) as int) ) as millisec

    FROM << trace_table >>

    where applicationname = '<< application_name >>'

    cpu -- duration -- millisec

    ---------------------------------------

    485302 --7685781173 --7687893

    This trace ran for just 3 minutes !!!!

    Can someone tell me which one is the one I'm looking for??

    I'm aware that the SUM on all applications can be larger as the total trace-duration due to simultantious logins (or pooling).

    Thanks in advance.

    Regrads,

    Guus

  • Duration is clock time. The amount of time elapsed between a query starting and a query completing. CPU time is the amount of time spent executing on the CPU, calculated per core.

    Which you use depends on which you want. Interested in what's taking time, use duration. Interested in what uses CPU, use CPU.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail,

    Thanks for the explanation.

    So - if I measure CPU = 150 running an 8-core server (maxdop 0) the total CPU will be 1200 ???

    Guus

  • gkramer 23701 (2/25/2014)


    So - if I measure CPU = 150 running an 8-core server (maxdop 0) the total CPU will be 1200

    No, 150.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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