Posted by Erland Sommarskog on 10/25/05 13:22

pardhi a via SQLMonster.com (u8956@uwe) writes:
> I am a dba for sql server recently we are facing problems with the no of
> connections.
>
> we have a database called ( x ) every day almost million users is using
> that databse after some time the cpu showing 100 percent utilization an
> error throwing like odbc error connection so in that case no one is able
> to connect to the server.when we pause it then restart the server for
> some time it looks good then again it will go to 100 % utilization.but
> there is no blocking occuring

You need to do some analysis to find out where this CPU usage is coming
from. Set up a Profiler trace which initially filters SQL:BatchCompleted
and SP:RPCCompleted for a events with a duration > 100 ms (or some other
limit you find reasonable). From that you may be able to dig down to
the exact queries that are causing problem.

As for running out of connections, this indicates a problem in the
application that never closes the connections. This may or may not be
related to the poor queries.

Is this a vendor application, or something which is developped in-house?


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

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

[Back to original message]