Saturday, February 25, 2012

CPU counter in SQL Profiler - milliseconds or meaningless?

Good evening,
I recently got out of a performance workshop here at SQL PASS in Orlando,
and was told by the presenter that the CPU counter in SQL Profiler does not
represent the total CPU time in milliseconds like BOL claims, but is in fact
a "meaningless" number. He says that he got this info from members of the SQL
Server team at Microsoft. While I have followed this gentleman's advice
before, I am skeptical about this claim. Several of the other folks in the
room agreed with him, but none could give me a reference to a site or
document to confirm this. I have done some searching, but have come up with
nothing. I would appreciate an "official" answer from one of the MS support
team.
My understanding has always been that CPU shows total CPU time, which can be
compared to Duration to help determine if a long-running query is slow
because of resource utilization (values are close to each other) or the query
has been slowed down by network, I/O or other "external" factors (CPU is
significantly lower than Duration). If my interpretation is wrong, I'd
greatly appreciate being set straight since I currently use this
thought-process when interpreting my own traces. I am now aware of the
RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
Thanks,
James
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
not
> represent the total CPU time in milliseconds like BOL claims, but is in
fact
> a "meaningless" number.
My understanding (which I'll be glad to see confirmed or refuted) is that
this number does indeed represent CPU milliseconds. However, the CPU number
shown in execution plans is "meaningless" (i.e., only relative).
|||Interesting, who presented this? You can come to my session on Friday about
SQL Trace Internals or come and see me at the Ask the Experts at PASS
The real question is if the CPU column for each event is really that useful?
Some of them in my opinion are not.
Anyhow CPU is calculated using the Win32 API GetThreadTimes (see
http://msdn.microsoft.com/library/de...hreadtimes.asp)
For every session this information is maintained on the PSS structure in
milliseconds.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> Good evening,
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
> not
> represent the total CPU time in milliseconds like BOL claims, but is in
> fact
> a "meaningless" number. He says that he got this info from members of the
> SQL
> Server team at Microsoft. While I have followed this gentleman's advice
> before, I am skeptical about this claim. Several of the other folks in the
> room agreed with him, but none could give me a reference to a site or
> document to confirm this. I have done some searching, but have come up
> with
> nothing. I would appreciate an "official" answer from one of the MS
> support
> team.
> My understanding has always been that CPU shows total CPU time, which can
> be
> compared to Duration to help determine if a long-running query is slow
> because of resource utilization (values are close to each other) or the
> query
> has been slowed down by network, I/O or other "external" factors (CPU is
> significantly lower than Duration). If my interpretation is wrong, I'd
> greatly appreciate being set straight since I currently use this
> thought-process when interpreting my own traces. I am now aware of the
> RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
> Thanks,
> James
|||Brad McGee of sql-server-performance.com was the presenter. Meanwhile, I
spoke to some of the PSS guys in the lab and they said that the CPU counter
does indeed represent milliseconds.
And yes, I will be at your session tomorrow. Thanks for your help!
James
"Gert E.R. Drapers" wrote:

> Interesting, who presented this? You can come to my session on Friday about
> SQL Trace Internals or come and see me at the Ask the Experts at PASS
> The real question is if the CPU column for each event is really that useful?
> Some of them in my opinion are not.
> Anyhow CPU is calculated using the Win32 API GetThreadTimes (see
> http://msdn.microsoft.com/library/de...hreadtimes.asp)
> For every session this information is maintained on the PSS structure in
> milliseconds.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2004 All rights reserved.
> "James Cook" <James Cook@.discussions.microsoft.com> wrote in message
> news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
>
>
|||Hi James,
There might be a little miscommunication here. Many of the PerfMon
counters, when they are measured per second or even per millisecond, are
cumulative rather than absolute values. So, let's say you're polling a
counter called 'widgets/sec' very 15 seconds. The values might come back as
1116, 1132, 1148, 1164, and so on and so on. Because this number is not an
absolute value, but a cumulative value, you might at first think that you've
got an ever increasing value. But in reality, the counter has a constant
absolute value of 16. You get to that conclusion by subtracting the last
polled value from the current polled value. So it's something you just have
to be careful about.
There is a Knowledge Base article that explains this quirk in greater
detail, including a list of all affected PerfMon counters. The article is
written by another SQL Server MVP, Geogh Hiten. Way to go Geoff! <g> Check
out:
http://support.microsoft.com/default...&Product=sql2k
Hope this helps,
-Kevin
SQL Server MVP
I support PASS, the Professional Association for SQL Server.
www.sqlpass.org
"James Cook" <James Cook@.discussions.microsoft.com> wrote in message
news:8E470F8F-E235-4D51-BAC4-C5A225E51972@.microsoft.com...
> Good evening,
> I recently got out of a performance workshop here at SQL PASS in Orlando,
> and was told by the presenter that the CPU counter in SQL Profiler does
not
> represent the total CPU time in milliseconds like BOL claims, but is in
fact
> a "meaningless" number. He says that he got this info from members of the
SQL
> Server team at Microsoft. While I have followed this gentleman's advice
> before, I am skeptical about this claim. Several of the other folks in the
> room agreed with him, but none could give me a reference to a site or
> document to confirm this. I have done some searching, but have come up
with
> nothing. I would appreciate an "official" answer from one of the MS
support
> team.
> My understanding has always been that CPU shows total CPU time, which can
be
> compared to Duration to help determine if a long-running query is slow
> because of resource utilization (values are close to each other) or the
query
> has been slowed down by network, I/O or other "external" factors (CPU is
> significantly lower than Duration). If my interpretation is wrong, I'd
> greatly appreciate being set straight since I currently use this
> thought-process when interpreting my own traces. I am now aware of the
> RPC:Completed showing 0 CPU bug, but that doesn't explain the rest.
> Thanks,
> James

No comments:

Post a Comment