CPU 100%

Hi,

I have sql server on a web server.
The machine grinds almost to a halt at peak times, as the asp pages are
causing sql server to use most of the cpu.
Looking at the graph it is at 100% for about 20 seconds at a time, then
drops, then goes back up.

Is there anything I can do to make sql server use less of the cpu? (Without
causing the asp pages to hand.)

Thanks

JJJJ (nospam) writes:
> I have sql server on a web server.
> The machine grinds almost to a halt at peak times, as the asp pages are
> causing sql server to use most of the cpu.
> Looking at the graph it is at 100% for about 20 seconds at a time, then
> drops, then goes back up.
> Is there anything I can do to make sql server use less of the cpu?

Probably. But exactly what I cannot tell. I can however give some
suggestions of what you can do to track down the problem. The best
approach is to use the Profiler. With this tool you can track commands
send to SQL Server, and you can get information about them. Assuming
that the source of the problem is a resource-intensive query, you
can use the Profiler to narrow down which query/ies it may be. Once
you have located one or more queries, you can look into how to improve
them, for instance by adding indexes.

Depending on your budget, you may also consider putting SQL Server on a
separate box, so that the web server and SQL Server does not have to
compete for resources.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
Many Thanks for the reply.
I am on the verge of investing in a new server anyway, as it seems easier to
throw more hardware at it, although I appreciate this is not necessarily the
best solution.
However, if I can get sql server on a separate box, hopefully my problems
will be solved!

JJ
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94ECF2D31D7D6Yazorman@.127.0.0.1...
> JJ (nospam) writes:
> > I have sql server on a web server.
> > The machine grinds almost to a halt at peak times, as the asp pages are
> > causing sql server to use most of the cpu.
> > Looking at the graph it is at 100% for about 20 seconds at a time, then
> > drops, then goes back up.
> > Is there anything I can do to make sql server use less of the cpu?
> Probably. But exactly what I cannot tell. I can however give some
> suggestions of what you can do to track down the problem. The best
> approach is to use the Profiler. With this tool you can track commands
> send to SQL Server, and you can get information about them. Assuming
> that the source of the problem is a resource-intensive query, you
> can use the Profiler to narrow down which query/ies it may be. Once
> you have located one or more queries, you can look into how to improve
> them, for instance by adding indexes.
> Depending on your budget, you may also consider putting SQL Server on a
> separate box, so that the web server and SQL Server does not have to
> compete for resources.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JJ (nospam) writes:
> I am on the verge of investing in a new server anyway, as it seems
> easier to throw more hardware at it, although I appreciate this is not
> necessarily the best solution.

Hey, if a new server costs 30000 crowns (a modest and cheap one), and
a consultant costs 1000 crowns an hour, and it takes him 40 hours to
track down the problem and fix it, throwing hardware on it may even
be a better solution.

Although judging from your description it sounds like you will still
have performance problems even with a new box, but hopefully less
severe - and maybe you can live with them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp