connection pooling problem

[Dan D.]

Let me say first that I don't know much about .NET. We inherited an app from
a group of developers and we have a second group of contractors working on
the app. The first group set min=100 in the config file. Since there will
only be about 15 users of the app we decided to see if we could lower that
number.

We reduced it from 100 to 60 then to 25. I was looking to see if any more
than that number show up in sql server. In every instance I noticed that the
number of connections in sql server is a little (from 1 to 6 connections)
over whatever amount is set in the config file. I've been wondering if the
app is opening new connections rather than using the ones that are already
there. The other connections in sql server show as "sleeping".

After we lowered the min to 25 we noticed that there were a lot more errors
in the app error log file that said "There is already an open DataReader
associated with this Connection which must be closed first." for a particular
page. I asked one of the contractors if he is sure that all of the
connections and datareaders are being closed. He thought so but wasn't
positive. We're going to check that again today.

We're also going to go back to min=100 to see if the number of errors on
this page go back down. If they do then we'll assume the two are related
somehow. I was also thinking of taking the min settng out of the config file
and just use the app defaults and see what happens.

Can anyone shed any light on what might be happening here?

Thanks,
--
Dan D.

[Göran Andersson]

If you only have 15 active users in a web site, there should be quite
few database connections in the pool. Definitely not a hundred, more
like three.

The only time there is any need for more than one connection is when the
server is creating more than one page at the same time, or when a page
uses more than one connection at a time.

The connection pool doesn't contain any more connections than what is
used, so there is really never any reason to lower the maximum size of
the pool. If the pool is filling up with connections, though, it's a
sign that there is some connections (or datareaders) that isn't closed
properly in the application.

Dan D. wrote:

Let me say first that I don't know much about .NET. We inherited an app from
a group of developers and we have a second group of contractors working on
the app. The first group set min=100 in the config file. Since there will
only be about 15 users of the app we decided to see if we could lower that
number.

We reduced it from 100 to 60 then to 25. I was looking to see if any more
than that number show up in sql server. In every instance I noticed that the
number of connections in sql server is a little (from 1 to 6 connections)
over whatever amount is set in the config file. I've been wondering if the
app is opening new connections rather than using the ones that are already
there. The other connections in sql server show as "sleeping".

After we lowered the min to 25 we noticed that there were a lot more errors
in the app error log file that said "There is already an open DataReader
associated with this Connection which must be closed first." for a particular
page. I asked one of the contractors if he is sure that all of the
connections and datareaders are being closed. He thought so but wasn't
positive. We're going to check that again today.

We're also going to go back to min=100 to see if the number of errors on
this page go back down. If they do then we'll assume the two are related
somehow. I was also thinking of taking the min settng out of the config file
and just use the app defaults and see what happens.

Can anyone shed any light on what might be happening here?

Thanks,

[Dan D.]

Thanks. We'll check again to be sure the datareaders and connections are
being closed properly.
--
Dan D.
"Göran Andersson" wrote:

If you only have 15 active users in a web site, there should be quite
few database connections in the pool. Definitely not a hundred, more
like three.

The only time there is any need for more than one connection is when the
server is creating more than one page at the same time, or when a page
uses more than one connection at a time.

The connection pool doesn't contain any more connections than what is
used, so there is really never any reason to lower the maximum size of
the pool. If the pool is filling up with connections, though, it's a
sign that there is some connections (or datareaders) that isn't closed
properly in the application.

Dan D. wrote:

Let me say first that I don't know much about .NET. We inherited an app from
a group of developers and we have a second group of contractors working on
the app. The first group set min=100 in the config file. Since there will
only be about 15 users of the app we decided to see if we could lower that
number.

We reduced it from 100 to 60 then to 25. I was looking to see if any more
than that number show up in sql server. In every instance I noticed that the
number of connections in sql server is a little (from 1 to 6 connections)
over whatever amount is set in the config file. I've been wondering if the
app is opening new connections rather than using the ones that are already
there. The other connections in sql server show as "sleeping".

After we lowered the min to 25 we noticed that there were a lot more errors
in the app error log file that said "There is already an open DataReader
associated with this Connection which must be closed first." for a particular
page. I asked one of the contractors if he is sure that all of the
connections and datareaders are being closed. He thought so but wasn't
positive. We're going to check that again today.

We're also going to go back to min=100 to see if the number of errors on
this page go back down. If they do then we'll assume the two are related
somehow. I was also thinking of taking the min settng out of the config file
and just use the app defaults and see what happens.

Can anyone shed any light on what might be happening here?

Thanks,