MS SQL 7 CPU Usage Problem

Hi all,

We have a VB App and MS SQL 7 driven web site running on a dual processor
server.

Lately, we've been suffering a problem whereby MS SQL Server seems to
consume all available CPU power. The web application grinds to an unusable
slow speed when this happens, and we tend to reboot the server in an attempt
to fix.

We have experimented with moving SQL Server onto just one processor and
turning off the option 'Boost SQL Server priority on Windows NT'. This did
not seem to help - so I have moved SQL Server back on to two processors.

We are running Windows 2000 Service pack 3. Any advise on how to manage CPU
usage with MS SQL Server and a VB web app on the same box would be much
appreciated.

Cheers,
Jerome.

jerome@.lonres.comHi Jerome,

exceessive CPU usage by MSSQL usually indicates one or more inefficient
Queries being run. Try to track down these Queries and see whether they
can be optimized (maybe another Index could help?).

Second, in your App, make sure that frequently used Queries are
parameterized. MSSQL tends to create execution plans for every single
ad-hoc Query which puts quite a load on both Memory and CPU.

hth
GB

MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.

I am currently encounter a problem whereby my sql 2000 server repetitively generates error 1501 (severity 20) -- Sort Failure.

The information available on the web only covers MS SQL 6.5. Apparently, I was informed that this error should not have encountered in MS SQL 2000.

So, if there is anybody out there that can help me in troubleshooting this problem, please do help me as I am really stuck.Hi,

which state does the error message tell you ?

http://www.lcard.ru/~nail/sybase/error/13243.htm

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||The state of the error message is 1. The database is MS SQL 2000 with SP3.

Unfortunately, the error state is not listed in the URL document you specified.|||

What is the usage of TEMPDB in this environment?

I guess that couldbe a problem check this KBA http://support.microsoft.com/kb/826433 that explains bits and piece of these errors. Confirm the service pack level on SQL Server.

MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.

I am currently encounter a problem whereby my sql 2000 server repetitively generates error 1501 (severity 20) -- Sort Failure.

The information available on the web only covers MS SQL 6.5. Apparently, I was informed that this error should not have encountered in MS SQL 2000.

So, if there is anybody out there that can help me in troubleshooting this problem, please do help me as I am really stuck.Hi,

which state does the error message tell you ?

http://www.lcard.ru/~nail/sybase/error/13243.htm

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||The state of the error message is 1. The database is MS SQL 2000 with SP3.

Unfortunately, the error state is not listed in the URL document you specified.|||

What is the usage of TEMPDB in this environment?

I guess that couldbe a problem check this KBA http://support.microsoft.com/kb/826433 that explains bits and piece of these errors. Confirm the service pack level on SQL Server.