CPU Using - Audit Logout

I have a VP app. that uses some queries from SQL Server 2000.
I'm noticing in SQL Profiler that the event "Audit Logout" is using alot of
cpu at times.
How can I decrease these CPU times ?Keep the connection open.
"Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
news:O9QSMS9wDHA.1996@.TK2MSFTNGP12.phx.gbl...
> I have a VP app. that uses some queries from SQL Server 2000.
> I'm noticing in SQL Profiler that the event "Audit Logout" is using alot
of
> cpu at times.
> How can I decrease these CPU times ?
>|||> Keep the connection open.
i cant keep the connection open.
Is there any other solution for this problem.
> "Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
> news:O9QSMS9wDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > I have a VP app. that uses some queries from SQL Server 2000.
> >
> > I'm noticing in SQL Profiler that the event "Audit Logout" is using alot
> of
> > cpu at times.
> > How can I decrease these CPU times ?
> >
> >
>|||My guess is that it is cleaning up a lot of server side cursors when it
spikes the cpu. Can you tell if the times when it happens if any of the
queries used server side over client side?
--
Andrew J. Kelly SQL MVP
"Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
news:O9QSMS9wDHA.1996@.TK2MSFTNGP12.phx.gbl...
> I have a VP app. that uses some queries from SQL Server 2000.
> I'm noticing in SQL Profiler that the event "Audit Logout" is using alot
of
> cpu at times.
> How can I decrease these CPU times ?
>|||We have a VB application. Over Timer control, we have run SQL queries. These
query types are, sometimes SELECT sometimes UPDATE. Everty time that app.
run, these queries executed. Every Connecion's Cursor Location is Client
Side. When we check SQL Profiler, Audit Logout events's CPU has been
increased. I think this is caused for operating systems CPU usage. How can
we decrease that high CPU usage or How can we decrease Audit Logout usage ?
Thanks a lot.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:OFpNTl%23wDHA.1908@.TK2MSFTNGP10.phx.gbl...
> My guess is that it is cleaning up a lot of server side cursors when it
> spikes the cpu. Can you tell if the times when it happens if any of the
> queries used server side over client side?
> --
> Andrew J. Kelly SQL MVP
>
> "Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
> news:O9QSMS9wDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > I have a VP app. that uses some queries from SQL Server 2000.
> >
> > I'm noticing in SQL Profiler that the event "Audit Logout" is using alot
> of
> > cpu at times.
> > How can I decrease these CPU times ?
> >
> >
>|||The logout is probably not using much time at all. More of a total. I know
this is true of duration, and I am pretty sure it is true of CPU. It is not
a perfect addition of all of the other rows, so I might be wrong, but I am
sure that the logout operation is not one of the more costly operations.
However, if applications are making and breaking too many connections, it
can become an issue.
--
----
--
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
"Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
news:O9QSMS9wDHA.1996@.TK2MSFTNGP12.phx.gbl...
> I have a VP app. that uses some queries from SQL Server 2000.
> I'm noticing in SQL Profiler that the event "Audit Logout" is using alot
of
> cpu at times.
> How can I decrease these CPU times ?
>|||But it's a cumulative count of the resources used for the lifetime of the
connection so whilst it may appear high it should be roughly the sum of the
CPU the connection used executing queries etc. Thus it being high or low
does not mean much, it's generally more interesting to look at those
statements that consumed most of the resources during the lifetime of the
connection if you see what I mean
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
news:O9QSMS9wDHA.1996@.TK2MSFTNGP12.phx.gbl...
> I have a VP app. that uses some queries from SQL Server 2000.
> I'm noticing in SQL Profiler that the event "Audit Logout" is using alot
of
> cpu at times.
> How can I decrease these CPU times ?
>|||Yes as Jasper points out this is a cumlative counter and will increase over
time as expected.
--
Andrew J. Kelly SQL MVP
"Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
news:%23L1$80%23wDHA.2316@.TK2MSFTNGP10.phx.gbl...
> We have a VB application. Over Timer control, we have run SQL queries.
These
> query types are, sometimes SELECT sometimes UPDATE. Everty time that app.
> run, these queries executed. Every Connecion's Cursor Location is Client
> Side. When we check SQL Profiler, Audit Logout events's CPU has been
> increased. I think this is caused for operating systems CPU usage. How can
> we decrease that high CPU usage or How can we decrease Audit Logout usage
?
>
> Thanks a lot.
>
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:OFpNTl%23wDHA.1908@.TK2MSFTNGP10.phx.gbl...
> > My guess is that it is cleaning up a lot of server side cursors when it
> > spikes the cpu. Can you tell if the times when it happens if any of the
> > queries used server side over client side?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
> > news:O9QSMS9wDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > > I have a VP app. that uses some queries from SQL Server 2000.
> > >
> > > I'm noticing in SQL Profiler that the event "Audit Logout" is using
alot
> > of
> > > cpu at times.
> > > How can I decrease these CPU times ?
> > >
> > >
> >
> >
>|||Ok. Firstly, Thank you very much. From Now on, what sholud we do ? Have you
any idea ?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:O8v2zj$wDHA.1500@.TK2MSFTNGP12.phx.gbl...
> But it's a cumulative count of the resources used for the lifetime of the
> connection so whilst it may appear high it should be roughly the sum of
the
> CPU the connection used executing queries etc. Thus it being high or low
> does not mean much, it's generally more interesting to look at those
> statements that consumed most of the resources during the lifetime of the
> connection if you see what I mean
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Tahir Ozmen" <tahirozmen(at)hotmail.com> wrote in message
> news:O9QSMS9wDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > I have a VP app. that uses some queries from SQL Server 2000.
> >
> > I'm noticing in SQL Profiler that the event "Audit Logout" is using alot
> of
> > cpu at times.
> > How can I decrease these CPU times ?
> >
> >
>

CPU unit in Profiler

What do the values for the CPU column in Profiler represent ? So if see 1000
, what exactly does it mean .. I believe its in ms.. but what does 1000 ms
for a CPU mean. I can understand 1000 ms for duration..
ThanksHassan,
CPU time is time spent on the CPU, running CPU cycles. Duration will be CPU
time plus time spent waiting for data reads or writes or other wait types.
Jon Jahren
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>|||Yes it is millis. And it is the number of milliseconds that the operation
spent on the processor.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>

CPU unit in Profiler

What do the values for the CPU column in Profiler represent ? So if see 1000
, what exactly does it mean .. I believe its in ms.. but what does 1000 ms
for a CPU mean. I can understand 1000 ms for duration..
Thanks
Hassan,
CPU time is time spent on the CPU, running CPU cycles. Duration will be CPU
time plus time spent waiting for data reads or writes or other wait types.
Jon Jahren
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>
|||Yes it is millis. And it is the number of milliseconds that the operation
spent on the processor.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>

CPU unit in Profiler

What do the values for the CPU column in Profiler represent ? So if see 1000
, what exactly does it mean .. I believe its in ms.. but what does 1000 ms
for a CPU mean. I can understand 1000 ms for duration..
ThanksHassan,
CPU time is time spent on the CPU, running CPU cycles. Duration will be CPU
time plus time spent waiting for data reads or writes or other wait types.
Jon Jahren
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>|||Yes it is millis. And it is the number of milliseconds that the operation
spent on the processor.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uePoHH8xEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What do the values for the CPU column in Profiler represent ? So if see
1000
> , what exactly does it mean .. I believe its in ms.. but what does 1000 ms
> for a CPU mean. I can understand 1000 ms for duration..
> Thanks
>

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

Cpu count in the Profiler and Task Manager

Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
Rich
Rich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich
|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:

> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus the
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
>
>

Cpu count in the Profiler and Task Manager

Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
RichRich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:

> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus t
he
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
>
>

Cpu count in the Profiler and Task Manager

Hello Group,
does anyone know if there is a relationship between the CPU count that is
displayed in the SQL Profiler and the Task Manager? I have a trace running
to show when a long insert takes places and the CPU column displays "16".
The same moment I have the Task Manager open and I am looking at the
SQLSERV.exe it displayes "40".
I thought that the numbers would be the same.
RichRich,
I think you're comparing CPU usage numbers for an individual SPID versus the
entire SQL Server application.
HTH
Jerry
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> Hello Group,
> does anyone know if there is a relationship between the CPU count that is
> displayed in the SQL Profiler and the Task Manager? I have a trace
> running
> to show when a long insert takes places and the CPU column displays "16".
> The same moment I have the Task Manager open and I am looking at the
> SQLSERV.exe it displayes "40".
> I thought that the numbers would be the same.
> Rich|||Hello Jerry,
hmmmm, if that is the case, seems like I could add up all of the SPID and it
would equal the total?
Rich
"Jerry Spivey" wrote:
> Rich,
> I think you're comparing CPU usage numbers for an individual SPID versus the
> entire SQL Server application.
> HTH
> Jerry
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEF40479-CBE6-4F6D-A668-641BFEEE8586@.microsoft.com...
> > Hello Group,
> >
> > does anyone know if there is a relationship between the CPU count that is
> > displayed in the SQL Profiler and the Task Manager? I have a trace
> > running
> > to show when a long insert takes places and the CPU column displays "16".
> > The same moment I have the Task Manager open and I am looking at the
> > SQLSERV.exe it displayes "40".
> >
> > I thought that the numbers would be the same.
> >
> > Rich
>
>

CPU cost to physical reads?

Me again, with yet another question/rant about performance tuning.
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.
> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.
> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan
> J.|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>> Me again, with yet another question/rant about performance tuning.
>> Is there a CPU cost, as reported to profiler, when a read is physical
>> and not logical?
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.
>> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
>> guess), start QA, start profiler, run big, fat query, takes 75 seconds
>> (including several recompiles), including 200k reads and 42 seconds of
>> CPU. The difference in time is presumably (and logically, from a cold
>> start) physical disk I/O.
>> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
>> is only 6 seconds.
>> Even if the compile-times all counted as CPU (does it?), that would
>> only be about 24 seconds, not enough to explain the earlier 42.
>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.
>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.
>> It sure seems like there is a correspondence between physical reads
>> and CPU time.
>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com

CPU cost to physical reads?

Me again, with yet another question/rant about performance tuning.
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.
see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.

> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.

> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan

> J.
|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.

>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.

>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.

>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.
|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com

CPU cost to physical reads?

Me again, with yet another question/rant about performance tuning.
Is there a CPU cost, as reported to profiler, when a read is physical
and not logical?
Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
guess), start QA, start profiler, run big, fat query, takes 75 seconds
(including several recompiles), including 200k reads and 42 seconds of
CPU. The difference in time is presumably (and logically, from a cold
start) physical disk I/O.
Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
is only 6 seconds.
Even if the compile-times all counted as CPU (does it?), that would
only be about 24 seconds, not enough to explain the earlier 42.
It sure seems like there is a correspondence between physical reads
and CPU time.
J.see inline
jxstern wrote:
> Me again, with yet another question/rant about performance tuning.
> Is there a CPU cost, as reported to profiler, when a read is physical
> and not logical?
The logical read is always there. The result of the physical read is
consumed through the data cache buffer.
My guess is that the CPU cost of a physical read depends on your I/O
subsystem.

> Boot up machine (workstation, XP, SQL2K sp0 yes I should upgrade it I
> guess), start QA, start profiler, run big, fat query, takes 75 seconds
> (including several recompiles), including 200k reads and 42 seconds of
> CPU. The difference in time is presumably (and logically, from a cold
> start) physical disk I/O.
> Rerun query, runs in 26 seconds, no recompiles (cached?), but the CPU
> is only 6 seconds.
> Even if the compile-times all counted as CPU (does it?), that would
> only be about 24 seconds, not enough to explain the earlier 42.
Let me get this straight. First run has an elapsed time of 75 seconds,
the second 26 seconds. Could be. Much of the data will be cached. If you
have a large resultset, then the network traffic would explain the
larger part of the remaining 26 seconds.
So the CPU cost seems to be 42 seconds for the first and 6 for the
seconds run. That probably means that the optimizer had a hard time
determining the best query plan. The second run it undoubtedly used the
cached query plan, leaving the other 5.9999 seconds to process the 200k
data from the buffer cache.

> It sure seems like there is a correspondence between physical reads
> and CPU time.
There might be, but I don't think this is the proper method to measure
this.
Gert-Jan

> J.|||On Wed, 17 Aug 2005 01:03:45 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>The logical read is always there. The result of the physical read is
>consumed through the data cache buffer.
>My guess is that the CPU cost of a physical read depends on your I/O
>subsystem.
OK, but I'd sort of assumed (!) it was zero cost, as far as the
profiler would know or care. My bad.

>Let me get this straight. First run has an elapsed time of 75 seconds,
>the second 26 seconds. Could be. Much of the data will be cached. If you
>have a large resultset, then the network traffic would explain the
>larger part of the remaining 26 seconds.
Small resultset, 30 small records.

>So the CPU cost seems to be 42 seconds for the first and 6 for the
>seconds run. That probably means that the optimizer had a hard time
>determining the best query plan. The second run it undoubtedly used the
>cached query plan, leaving the other 5.9999 seconds to process the 200k
>data from the buffer cache.
I can't believe the optimizer had *that* hard a time. Once the data
is cached, I've *never* seen a recompile (based on changed code) take
more than a few seconds. So, the rest of that reading seems to be CPU
corresponding to physical IO.
Additional observation this morning, on a busy dev server, someone
doing a big, busy convert on database A, when I go to database B and
run what should be a quick query, the CPU and duration are both very
large. Looks like my query is getting charged for all sorts of
database loading overhead, that, again, I would have thought to be
invisible at the profiler level.

>There might be, but I don't think this is the proper method to measure
>this.
The only point I want to confirm here is that before one trusts in
profiler results, one should try to get a steady-state, or otherwise
normalize the load, because otherwise all sorts of noise is going to
distort the numbers. It's probably hopeless to measure or quantify it
further. It would be nice if Microsoft would document that these are
valid concerns, perhaps enhance the system so that these factors are
made visible or eliminated or something, though I guessing such
improvements might be very difficult!
J.|||jxstern wrote:
> The only point I want to confirm here is that before one trusts in
> profiler results, one should try to get a steady-state, or otherwise
> normalize the load, because otherwise all sorts of noise is going to
> distort the numbers. It's probably hopeless to measure or quantify it
> further. It would be nice if Microsoft would document that these are
> valid concerns, perhaps enhance the system so that these factors are
> made visible or eliminated or something, though I guessing such
> improvements might be very difficult!
>
That's not what I've observed. CPU should be pretty consistent given the
same execution plan and parameter set. It's possible physical reads
could add some overhead to the query. It's also possible the load on the
server is forcing SQL Server to use another execution plan. It would
help if you could check the execution plan to be sure it is the same
with and without load with the same parameters and then post the
Profiler results here.
David Gugick
Quest Software
www.imceda.com
www.quest.com

CPU and Duration don't match in SQL Server Profiler

Hello All,

I have one store proc that is really slowing down my SQL server, at least according to the SQL Profiler. When I run the profiler and choose to show store procs that take longer then 3000 ms to execute, this is the only precedure that shows up. The CPU time and Duration is both in the 3000 ms mark. When I open up Query Analyzer and run that same store proc it takes less then a second, but when I check the Profiler to see the time, it's Blank (yellow) .. I'm using SQL 2005 Profiler. Any ideas why

1. I don't see a time when I execute the query from the profiler?

2. It says it takes 3 seconds when I only see it taking less then one?

Thank you

This is probably 3000 microseconds (3 milliseconds) :)

"In SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10^-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10^-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds."

Books Online:
http://msdn2.microsoft.com/en-us/library/ms175848.aspx

SQL Server Enthusiast:
http://weblogs.sqlteam.com/tarad/archive/2006/10/04/13621.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

CPU

I would highly apprecite if any one guide me regarding CPU %, I check in the
task manager the CPU % goes to 100 and when I run the Profiler to findout
the query which query taking longer time then I didn'd find any query, when
I saw the Profiler all the SPs taking 0% CPU usage... what would be reason,
why CPU goes to 100%, any idea ?
Thanks
Profiler can add quite a load to an already busy server. You should create
a server side trace that traces directly to a file on a local drive to
minimize impact on the server. You can then import that trace into a table
or profiler for viewing later. Check out sp_tracecreate in BooksOnLine.
Alternately you can set up profiler how you want and then click on File -
Script Trace as 2000 to have it generate most of the code for you.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:eyLJuhtPGHA.140@.TK2MSFTNGP12.phx.gbl...
>I would highly apprecite if any one guide me regarding CPU %, I check in
>the task manager the CPU % goes to 100 and when I run the Profiler to
>findout the query which query taking longer time then I didn'd find any
>query, when I saw the Profiler all the SPs taking 0% CPU usage... what
>would be reason, why CPU goes to 100%, any idea ?
> Thanks
>
|||Just want to add that the longest running query may not necessarily be the
query or queries that consume the most CPU or primarily responsible for
driving your processors to 100%.
Linchi
"Rogers" wrote:

> I would highly apprecite if any one guide me regarding CPU %, I check in the
> task manager the CPU % goes to 100 and when I run the Profiler to findout
> the query which query taking longer time then I didn'd find any query, when
> I saw the Profiler all the SPs taking 0% CPU usage... what would be reason,
> why CPU goes to 100%, any idea ?
> Thanks
>
>
|||Rogers wrote:
> I would highly apprecite if any one guide me regarding CPU %, I check
> in the task manager the CPU % goes to 100 and when I run the Profiler
> to findout the query which query taking longer time then I didn'd
> find any query, when I saw the Profiler all the SPs taking 0% CPU
> usage... what would be reason, why CPU goes to 100%, any idea ?
> Thanks
Are you sure it's the SQL Server service that's driving the CPU up? If
so, you can create a trace 9server-side preferred) and look for
SQL:BatchCompleted / RPC:Completed, and add a CPU filter of say, 500 to
start in order to see the high CPU users. Adjust the filter as needed.
As Linchi stated, long duration does not translate to high cpu. You
could have a highly parallel query run for a short time and consume a
lot of CPU. You could also have a long running transaction that consume
little CPU. You need to look at both metrics as they are both important
intheir own way.
David Gugick - SQL Server MVP
Quest Software
|||Thanks for your reply, how I can check the long running query? is there any
way I can check..
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ufXajvVQGHA.5248@.TK2MSFTNGP09.phx.gbl...
> Rogers wrote:
> Are you sure it's the SQL Server service that's driving the CPU up? If so,
> you can create a trace 9server-side preferred) and look for
> SQL:BatchCompleted / RPC:Completed, and add a CPU filter of say, 500 to
> start in order to see the high CPU users. Adjust the filter as needed. As
> Linchi stated, long duration does not translate to high cpu. You could
> have a highly parallel query run for a short time and consume a lot of
> CPU. You could also have a long running transaction that consume little
> CPU. You need to look at both metrics as they are both important intheir
> own way.
> --
> David Gugick - SQL Server MVP
> Quest Software
>
|||Rogers wrote:
> Thanks for your reply, how I can check the long running query? is
> there any way I can check..
>
With duration from the trace using the SQL:BatchCompleted or RPC:Completed
events or SP:StmtCompleted or SQL:StmtCompleted at a lower level.
David Gugick - SQL Server MVP
Quest Software