Connection pool maxed out

  • Thread starter Thread starter VS_NET_DEV
  • Start date Start date
V

VS_NET_DEV

Hi All,
I see several postings regarding this and did not find an answer...
I am running ASP.NET 1.1 on Win2003 (IIS 6.0)
The Max connections in the pool is set to 100. I checked and 100% sure
*ALL* connections are closed within code.
But still I see that the connecitions keep rising and finally hit
100 and the system hangs.
In tests, I see that there are some long running queries (ie. > 1
sec).

Q1. Do long running queries affect the connection pool? Do we need to
isolate them in different pools?
Q2. I re-use the same connection string etc.. But what is preventing
from re-using the connection pool? On the SQL profiler, there are
several connecitions simply "sleeping" state, but still New
connections are opened.. (sometimes, the connections are resued and
but when the system is busy i.e. all users logged in, the connections
are not reused..

any help would be appreciated....

Thanks
Jenny
 
Hi Jenny,

"Closed" or "Disposed"? Closing a connection doesn't kill it off by default.
It just returns it to the pool. You can disable this by adding Pooling=false
to your connection string (if using sql client).

HTH,
 
What if you "dispose" also the connection ? I'm not sure closing is enough
to return the connection to the pool.
 
¤ What if you "dispose" also the connection ? I'm not sure closing is enough
¤ to return the connection to the pool.
¤

Dispose is unnecessary if you explicitly call Close.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Then double check your code in particular DataReaders...

For example your DB layer could register connexions in the "Item"
collection, and you could check in teh EndRequest event that they are
propery closed...
 
thanks everyone. The tried both close and Dispose- the results are the
same.
The questios remain:

Q1. Do long running queries affect the connection pool? Do we need to
isolate them in different pools?
Q2. I re-use the same connection string etc.. But what is preventing
from re-using the connection pool?

Additionally: Paul, Can you post any sample code to register the
connections and check them at Request End?

Thanks in advance..

-Jenny
 
The connections are "closed". Yes, I want to use the connection pool
and the connections are returned to the pool. But there are some long
running queries and the the connections are not re-used , rather new
connections are open and eventually maxed out...

Thanks a lot in advance..
_jenny
 
Jenny,
Q1. Do long running queries affect the connection pool?
Click to expand...
Pooling works better when you can quickly return the connection to the pool,
long running queries keep the connection busy longer so yes, pooling is
affected.
Do we need to isolate them in different pools?
Click to expand...
No, they will take the same space in one pool or two, there should not be
any benefit from isolating them.
Q2. I re-use the same connection string etc.. But what is preventing
from re-using the connection pool?
Click to expand...
Pools are per process, per app-domain, per user/per connection string.

With long running queries it is possible to max out the connection pool, you
would need 100 long running queries (longer than 15 seconds) to happen
concurrently. All the 100 concurrent queries must take longer than 15
seconds since the pooler will attempt to get a connection for 15 seconds on
Open.

In this scenario your 100 queries will keep all the connections in the pool
busy. When I try to open the one hundred and first connection I will wait
for 15 seconds to get a connection, if none of the queries complete in this
time (if you don't close any of the 100 connections) then we will timeout
with the exception you see. I have never seen anything even close to this
on a production server, we have run 5k clients on an ado.net app with max
pool size of 10 without ever coming close to maxing out the pooler.

You mention that you are certain that you are closing the connection, are
you using the "using" construct or a try finally block to guarantee this?

It is almost certain that connections are leaking, and there are three
likely culprits. Ado.net, Visual Studio and exceptions in your code. To fix
the first two make sure that you have the latest version of the framework or
all the applicable QFEs if you are using v1.0, there where two issues where
Oracle Client connections could leak if an anti-virus or a process unload
happened while connections where in the pool, VS also had an issue when
running asp.net apps in debug mode and hitting re-start, these issues have
been fixed in the latest release.

The third issue is as follows:
Con.Open()
//use connection, this sometimes throws exception.
Con.Close() //does not get called when an exception is thrown.

To fix this problem make sure that you use the "using" construct or a try
finally block like this:
try{
Con.Open()
//use connection, this sometimes throws exception.
}finally{
Con.Close() //gets called even when an exception is thrown above
}

Hope this helps,
 
Hi all,
thank you so much.. I will take a look at the long running queries and
tweak them.

Thanks again.
-Benjamin
 
Back
Top