SQL Server Performance Forum – Threads Archive
SQL trace interpretation – CPU exceeds duration
I have a SQL trace that I’m analyzing with Read80Trace and I noticed that I have a single transaction that seems to be doing something impossible. The CPU counter is higher than the duration counter. This is a quad processor box, but CPU is 4.04 times the duration. Is there some kind of pad in the CPU count? This is not a fast query. Its average execution length is 60-70 secs on a production server running an average of 5000 trans/min, so it seems like a stretch for this single query to take 100% of all 4 processors for over a minute. This query typically has a duration of 65000, CPU 160000, Reads 1400000 and a few Writes. The ‘bad’ query had the following stats from Read80Trace: SPID195 Start and End Time
2005-04-22 10:14:35.690
2005-04-22 10:15:39.737 Duration (ms)
64046 CPU (ms)
258903 Reads
2035684 Writes
7 Was RPC
False Query Format
EXEC PC_PROCNAME @VARS = {##}, etc. Trace File
422051006_68.trc Example Query
EXEC PC_PROCNAME @VARS = 6, etc.
Is no frequently, but CPU could be higher than Duration, specially if there are others queries using CPU. I’ve review my traces and found few cases, in queries without optimization. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
]]>