Runaway CPU

For some reason, for two days now, in the morning hours
the sqlservr.exe process is using an average of 94% of the
CPU on a very powerful server.
Can anyone tell me how to determine what the problem is,
without just rebooting the server?
Here are some Buffer Manager statistics in case it helps:
Buffer cache hit ratio 3044
Buffer cache hit ratio base 3048
Page lookups/sec 405446548
Free list stalls/sec 114
Free pages 291
Total pages 207872
Target pages 207872
Database pages 190592
Reserved pages 648
Stolen pages 16989
Lazy writes/sec 1722
Readahead pages/sec 477294
Procedure cache pages 14862
Page reads/sec 616224
Page writes/sec 658045
Checkpoint pages/sec 315881
Thanks.
Allen"Allen White" <awhite_nospam@.advanstar.com> wrote in message
news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
> For some reason, for two days now, in the morning hours
> the sqlservr.exe process is using an average of 94% of the
> CPU on a very powerful server.
> Can anyone tell me how to determine what the problem is,
> without just rebooting the server?
> Here are some Buffer Manager statistics in case it helps:
> Buffer cache hit ratio 3044
> Buffer cache hit ratio base 3048
> Page lookups/sec 405446548
> Free list stalls/sec 114
> Free pages 291
> Total pages 207872
> Target pages 207872
> Database pages 190592
> Reserved pages 648
> Stolen pages 16989
> Lazy writes/sec 1722
> Readahead pages/sec 477294
> Procedure cache pages 14862
> Page reads/sec 616224
> Page writes/sec 658045
> Checkpoint pages/sec 315881
> Thanks.
> Allen
>
Turn on Profiler..
What jobs (if any) are running? Do you have maintenance plans executing
that are rebuilding indexes?
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, Rick. No maintenance plans are running. I keep a
trace running on all my production servers and have
reviewed the trace files and can see nothing that would
cause that kind of CPU activity. (The trace captures just
Batch Complete and RPC Complete activity.) I see lots and
lots of transactions, but nothing running for long periods
of time, nor anything that would be processor intensive.
Allen
>--Original Message--
>"Allen White" <awhite_nospam@.advanstar.com> wrote in
message
>news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
>> For some reason, for two days now, in the morning hours
>> the sqlservr.exe process is using an average of 94% of
the
>> CPU on a very powerful server.
>> Can anyone tell me how to determine what the problem is,
>> without just rebooting the server?
>> Here are some Buffer Manager statistics in case it
helps:
>> Buffer cache hit ratio 3044
>> Buffer cache hit ratio base 3048
>> Page lookups/sec 405446548
>> Free list stalls/sec 114
>> Free pages 291
>> Total pages 207872
>> Target pages 207872
>> Database pages 190592
>> Reserved pages 648
>> Stolen pages 16989
>> Lazy writes/sec 1722
>> Readahead pages/sec 477294
>> Procedure cache pages 14862
>> Page reads/sec 616224
>> Page writes/sec 658045
>> Checkpoint pages/sec 315881
>> Thanks.
>> Allen
>Turn on Profiler..
>What jobs (if any) are running? Do you have maintenance
plans executing
>that are rebuilding indexes?
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Runaway CPU

For some reason, for two days now, in the morning hours
the sqlservr.exe process is using an average of 94% of the
CPU on a very powerful server.
Can anyone tell me how to determine what the problem is,
without just rebooting the server?
Here are some Buffer Manager statistics in case it helps:
Buffer cache hit ratio3044
Buffer cache hit ratio base3048
Page lookups/sec405446548
Free list stalls/sec114
Free pages291
Total pages207872
Target pages207872
Database pages190592
Reserved pages648
Stolen pages16989
Lazy writes/sec1722
Readahead pages/sec477294
Procedure cache pages14862
Page reads/sec616224
Page writes/sec658045
Checkpoint pages/sec315881
Thanks.
Allen
"Allen White" <awhite_nospam@.advanstar.com> wrote in message
news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
> For some reason, for two days now, in the morning hours
> the sqlservr.exe process is using an average of 94% of the
> CPU on a very powerful server.
> Can anyone tell me how to determine what the problem is,
> without just rebooting the server?
> Here are some Buffer Manager statistics in case it helps:
> Buffer cache hit ratio 3044
> Buffer cache hit ratio base 3048
> Page lookups/sec 405446548
> Free list stalls/sec 114
> Free pages 291
> Total pages 207872
> Target pages 207872
> Database pages 190592
> Reserved pages 648
> Stolen pages 16989
> Lazy writes/sec 1722
> Readahead pages/sec 477294
> Procedure cache pages 14862
> Page reads/sec 616224
> Page writes/sec 658045
> Checkpoint pages/sec 315881
> Thanks.
> Allen
>
Turn on Profiler..
What jobs (if any) are running? Do you have maintenance plans executing
that are rebuilding indexes?
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks, Rick. No maintenance plans are running. I keep a
trace running on all my production servers and have
reviewed the trace files and can see nothing that would
cause that kind of CPU activity. (The trace captures just
Batch Complete and RPC Complete activity.) I see lots and
lots of transactions, but nothing running for long periods
of time, nor anything that would be processor intensive.
Allen

>--Original Message--
>"Allen White" <awhite_nospam@.advanstar.com> wrote in
message[vbcol=seagreen]
>news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
the[vbcol=seagreen]
helps:
>Turn on Profiler..
>What jobs (if any) are running? Do you have maintenance
plans executing
>that are rebuilding indexes?
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Runaway CPU

For some reason, for two days now, in the morning hours
the sqlservr.exe process is using an average of 94% of the
CPU on a very powerful server.
Can anyone tell me how to determine what the problem is,
without just rebooting the server?
Here are some Buffer Manager statistics in case it helps:
Buffer cache hit ratio 3044
Buffer cache hit ratio base 3048
Page lookups/sec 405446548
Free list stalls/sec 114
Free pages 291
Total pages 207872
Target pages 207872
Database pages 190592
Reserved pages 648
Stolen pages 16989
Lazy writes/sec 1722
Readahead pages/sec 477294
Procedure cache pages 14862
Page reads/sec 616224
Page writes/sec 658045
Checkpoint pages/sec 315881
Thanks.
Allen"Allen White" <awhite_nospam@.advanstar.com> wrote in message
news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
> For some reason, for two days now, in the morning hours
> the sqlservr.exe process is using an average of 94% of the
> CPU on a very powerful server.
> Can anyone tell me how to determine what the problem is,
> without just rebooting the server?
> Here are some Buffer Manager statistics in case it helps:
> Buffer cache hit ratio 3044
> Buffer cache hit ratio base 3048
> Page lookups/sec 405446548
> Free list stalls/sec 114
> Free pages 291
> Total pages 207872
> Target pages 207872
> Database pages 190592
> Reserved pages 648
> Stolen pages 16989
> Lazy writes/sec 1722
> Readahead pages/sec 477294
> Procedure cache pages 14862
> Page reads/sec 616224
> Page writes/sec 658045
> Checkpoint pages/sec 315881
> Thanks.
> Allen
>
Turn on Profiler..
What jobs (if any) are running? Do you have maintenance plans executing
that are rebuilding indexes?
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, Rick. No maintenance plans are running. I keep a
trace running on all my production servers and have
reviewed the trace files and can see nothing that would
cause that kind of CPU activity. (The trace captures just
Batch Complete and RPC Complete activity.) I see lots and
lots of transactions, but nothing running for long periods
of time, nor anything that would be processor intensive.
Allen

>--Original Message--
>"Allen White" <awhite_nospam@.advanstar.com> wrote in
message
>news:21b501c4d885$f31e0f60$a501280a@.phx.gbl...
the[vbcol=seagreen]
helps:[vbcol=seagreen]
>Turn on Profiler..
>What jobs (if any) are running? Do you have maintenance
plans executing
>that are rebuilding indexes?
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

run profiler when high cpu

I want to have profiler run for a period of time - 5 minutes - when system
cpu exceeds 90%. This would happen automatically. Can this be done?
My suggestion would be to add an alert to Performance Monitor to detect the
> 90% CPU, and then have it execute 'the relevant SQL' to start a
server-side profile. See
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm for the
latter. The '5 minutes' would just be a case of storing the trace start
time somewhere, and having a SQL job that runs intermittently that stops the
trace after the required time.
"nickI" <nickI@.discussions.microsoft.com> wrote in message
news:184E59AD-FE7A-40FB-9783-BAAA17BF0348@.microsoft.com...
>I want to have profiler run for a period of time - 5 minutes - when system
> cpu exceeds 90%. This would happen automatically. Can this be done?

Run a DTS Package "in the background" or with low priority?

Configuration:
Windows 2000
Sql Server 2000
Quad CPU
Problem:
I have a DTS update operation that takes many hours to run. The
process is monthly, and for that process speed is not an issue; however,
the database server hosts multiple databases, some with applications
that do interactive queries. The DTS Update step brings down the speed
of interactive queries on the database being updated and all other
databases dramatically.
Is there any way to lower the priority of a DTS package operation so
that it does not impact interactive queries on that databases and other
databases running on the server?
Basically, can I run a DTS package "in the background" or with a very
low priority?Hi
You may want to read The Guru's Guide to SQL Server Architecture and
Internals by Ken Henderson ISDN 0-201-70047-6 which goes into depth on
thread scheduling. You may get some benefit by setting the MAXDOP query hint
on some of your statements. Highlighting what part of your process is slow
and why it is slow may help you to provide a more efficient solution.
You may also want to review the architecture of this process and either
split it into smaller independent chunks that can be run separately, or if
the data created by this process is read-only to the other processes, then
you may want to look at doing it offline and then swapping it in when the
process is complete e.g. having it all in a separate read-only database.
John
"John Bailo" <jabailo@.texeme.com> wrote in message
news:gsudneQq3ZlEYAzeRVn-uw@.speakeasy.net...
> Configuration:
> Windows 2000
> Sql Server 2000
> Quad CPU
>
> Problem:
> I have a DTS update operation that takes many hours to run. The process
> is monthly, and for that process speed is not an issue; however,
> the database server hosts multiple databases, some with applications that
> do interactive queries. The DTS Update step brings down the speed of
> interactive queries on the database being updated and all other databases
> dramatically.
> Is there any way to lower the priority of a DTS package operation so that
> it does not impact interactive queries on that databases and other
> databases running on the server?
> Basically, can I run a DTS package "in the background" or with a very low
> priority?
>
>