MS SQL 7 CPU Usage Problem

[Jerome]

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.c om

[GB]

Re: MS SQL 7 CPU Usage Problem

Hi 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