SQL Server 2000 SP4 Becomes clogged!!!
in [Programming SQL]
|
From: Query Builder on 20 Jul 2008 15:21 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.
From: Dan Guzman on 20 Jul 2008 15:41 > 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(a)gmail.com> wrote in message news:9157f7ff-0909-43dc-b2c1-d1291751ad2a(a)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.
From: Erland Sommarskog on 20 Jul 2008 17:37 Query Builder (querybuilder(a)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(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: TheSQLGuru on 20 Jul 2008 20:41 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(a)gmail.com> wrote in message news:9157f7ff-0909-43dc-b2c1-d1291751ad2a(a)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.
|
Pages: 1 Prev: Normalization Techniques Next: Iterating XML in SQL Server 2005 (or 2008) stored procedure |