too many connections

[pardhi a via SQLMonster.com]

Hi

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

if anyone knows this problem please help
your help would be appreciated.

Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200510/1

[Robert Klemme]

pardhi a via SQLMonster.com wrote:

Hi

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
Concurrently?
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

Sounds like you have a resource problem. 1,000,000 concurrent users need
some resources (mainly mem) for managing them alone. If they start doing
transactions this will likely increase. Maybe your connections aren't
released properly afer work is done or your system is just undersized.

Kind regards

robert

[pardhi a via SQLMonster.com]

currently we are using 2 plus gb memory

Robert Klemme wrote:

Hi

[quoted text clipped - 3 lines]

we have a database called ( x ) every day almost million users is
using that databse

Concurrently?

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

Sounds like you have a resource problem. 1,000,000 concurrent users need
some resources (mainly mem) for managing them alone. If they start doing
transactions this will likely increase. Maybe your connections aren't
released properly afer work is done or your system is just undersized.

Kind regards

robert

--
Message posted via http://www.sqlmonster.com

[pardhi a via SQLMonster.com]

currently we are using 2 plus memory
and db size is about 40 gb
recovery model is simple.

pardhi a wrote:

currently we are using 2 plus gb memory

Hi

[quoted text clipped - 18 lines]

robert

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200510/1

[Erland Sommarskog]

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, es****@sommarskog.se

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