I have a SQL Server 2000 SP4 Enterprise Edition with Build 8.00.2039.
OS: Microsoft Windows Server 2003 Ent. SP2. 6GB Ram and AWE is enabled
for SQL Server to use more than 4 GB.

The server hosts some reporting databases where Business Objects
execute stored procedures to pull data for these reports.

All of a sudden the server is acting up. I have noticed some queries
running and not completing. Non of them are locking any other. But the
server is very slow in response. It takes awhile to return SP_WHO2.
The procedures which takes normally 2-3 minutes are hanging. The only
way to get the machine back to normal is to kill these reports or
restart SQL Server. During this time the CPU is maxed out and 100%
utilized. SQL Server 2000 Process Counters: Processor Time 100 %. Not
a lot of I/O activity reported. Task manager shows SQL is the only
thing eating up the memory.

So far I have tried the followings: I have dropped and recreated all
indexes. updated statistics. Ran DBCC diagnosis like DBCC CHECKDB and
DBCC CHECKTable. I have dropped and created the views. I have flushed
the procedure cache and forced procedures to recompile. I have
compacted the database. I have checked the SQL and the Windows Event
logs. Nothing is out of ordinary. No Network latency issues noticed. I
have ran PerfMon traces on SQL Counters and noticed nothing unusual.
No patches have been applied recently.

Has anyone have any clue on what may be the issue? Is there a patch or
a hot fix by MS to resolve any of the issues?

Please let me know if any additional information can help and I'd be
glad to provide them.

Any thoughts/suggestions will be greatly appreciated.

Thanks in Advance,
Aravin Rajendra.

> During this time the CPU is maxed out and 100%
> utilized. SQL Server 2000 Process Counters: Processor Time 100 %. Not
> a lot of I/O activity reported. Task manager shows SQL is the only
> thing eating up the memory.

Perhaps you simply have a bad query that consumes a lot of CPU. In the case
of an intensive parallel plan, sp_who2 will report multiple rows with the
same spid. You can then identify the problem query with DBCC INPUTBUFFER.

You can also identify CPU-intensive queries by calculating the delta CPU
usage if individual sessions between sp_who2 invocations. Another method to
identify high CPU queries is with a SQL trace of batch and rpc completed
events with a filter on CPU.

Once you identify the CPU bound query, you can examine the execution plan
and perform index/query tuning.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Query Builder" <querybuilder@gmail.com> wrote in message
news:9157f7ff-0909-43dc-b2c1-d1291751ad2a@w7g2000hsa.googlegroups.com...
>I have a SQL Server 2000 SP4 Enterprise Edition with Build 8.00.2039.
> OS: Microsoft Windows Server 2003 Ent. SP2. 6GB Ram and AWE is enabled
> for SQL Server to use more than 4 GB.
>
> The server hosts some reporting databases where Business Objects
> execute stored procedures to pull data for these reports.
>
> All of a sudden the server is acting up. I have noticed some queries
> running and not completing. Non of them are locking any other. But the
> server is very slow in response. It takes awhile to return SP_WHO2.
> The procedures which takes normally 2-3 minutes are hanging. The only
> way to get the machine back to normal is to kill these reports or
> restart SQL Server. During this time the CPU is maxed out and 100%
> utilized. SQL Server 2000 Process Counters: Processor Time 100 %. Not
> a lot of I/O activity reported. Task manager shows SQL is the only
> thing eating up the memory.
>
> So far I have tried the followings: I have dropped and recreated all
> indexes. updated statistics. Ran DBCC diagnosis like DBCC CHECKDB and
> DBCC CHECKTable. I have dropped and created the views. I have flushed
> the procedure cache and forced procedures to recompile. I have
> compacted the database. I have checked the SQL and the Windows Event
> logs. Nothing is out of ordinary. No Network latency issues noticed. I
> have ran PerfMon traces on SQL Counters and noticed nothing unusual.
> No patches have been applied recently.
>
> Has anyone have any clue on what may be the issue? Is there a patch or
> a hot fix by MS to resolve any of the issues?
>
> Please let me know if any additional information can help and I'd be
> glad to provide them.
>
> Any thoughts/suggestions will be greatly appreciated.
>
> Thanks in Advance,
> Aravin Rajendra.

Query Builder (querybuilder@gmail.com) writes:
> All of a sudden the server is acting up. I have noticed some queries
> running and not completing. Non of them are locking any other. But the
> server is very slow in response. It takes awhile to return SP_WHO2.
> The procedures which takes normally 2-3 minutes are hanging. The only
> way to get the machine back to normal is to kill these reports or
> restart SQL Server. During this time the CPU is maxed out and 100%
> utilized. SQL Server 2000 Process Counters: Processor Time 100 %. Not
> a lot of I/O activity reported. Task manager shows SQL is the only
> thing eating up the memory.

Have you looked in the SQL Server Errorlog to see if there are any
messages?

I have a feeling what it could be, but I like to have it confirmed.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Are you sure you didn't have auto-updates on and the recent microsoft
security patch for sql server 2000 didn't install itself?


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Query Builder" <querybuilder@gmail.com> wrote in message
news:9157f7ff-0909-43dc-b2c1-d1291751ad2a@w7g2000hsa.googlegroups.com...
>I have a SQL Server 2000 SP4 Enterprise Edition with Build 8.00.2039.
> OS: Microsoft Windows Server 2003 Ent. SP2. 6GB Ram and AWE is enabled
> for SQL Server to use more than 4 GB.
>
> The server hosts some reporting databases where Business Objects
> execute stored procedures to pull data for these reports.
>
> All of a sudden the server is acting up. I have noticed some queries
> running and not completing. Non of them are locking any other. But the
> server is very slow in response. It takes awhile to return SP_WHO2.
> The procedures which takes normally 2-3 minutes are hanging. The only
> way to get the machine back to normal is to kill these reports or
> restart SQL Server. During this time the CPU is maxed out and 100%
> utilized. SQL Server 2000 Process Counters: Processor Time 100 %. Not
> a lot of I/O activity reported. Task manager shows SQL is the only
> thing eating up the memory.
>
> So far I have tried the followings: I have dropped and recreated all
> indexes. updated statistics. Ran DBCC diagnosis like DBCC CHECKDB and
> DBCC CHECKTable. I have dropped and created the views. I have flushed
> the procedure cache and forced procedures to recompile. I have
> compacted the database. I have checked the SQL and the Windows Event
> logs. Nothing is out of ordinary. No Network latency issues noticed. I
> have ran PerfMon traces on SQL Counters and noticed nothing unusual.
> No patches have been applied recently.
>
> Has anyone have any clue on what may be the issue? Is there a patch or
> a hot fix by MS to resolve any of the issues?
>
> Please let me know if any additional information can help and I'd be
> glad to provide them.
>
> Any thoughts/suggestions will be greatly appreciated.
>
> Thanks in Advance,
> Aravin Rajendra.