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