Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Duration per application as captured within a TRACE Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 7:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:12 AM
Points: 15, Visits: 90
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
Post #1544894
Posted Tuesday, February 25, 2014 7:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1544914
Posted Tuesday, February 25, 2014 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:12 AM
Points: 15, Visits: 90
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
Post #1544922
Posted Tuesday, February 25, 2014 7:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1544926
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse