Pooling issues

We have been fighting with this for some time now. The web group keeps
setting the max connections in their connection strings to "2500" yes you
read that correct....that seems a little insane. BUT, we've witnesses the
site stop working when they lower it. The behavior we are seeing would
point at the pool manager not queuing requests and servicing them properly
when a connection becomes available.
Watching the activity monitor in SQL2005 we rarely see more than 5-6
connections in a runnable state. Although there are many more sleeping.
All the code in the web apps and webservices has been exhaustively gone over
to ensure connections are being released and in a timely manner. What else
could we be missing?OK so sleeping processes are only waiting for locks and are actually
busy....I misready something there. BUT why do some of these show wait
times of zero when the last batch value is sometimes a day ago? If it
hasn't run something since yesterday shouldn't the connection be gone by
now?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23zJKeBrbGHA.3908@.TK2MSFTNGP02.phx.gbl...
> We have been fighting with this for some time now. The web group keeps
> setting the max connections in their connection strings to "2500" yes you
> read that correct....that seems a little insane. BUT, we've witnesses
> the site stop working when they lower it. The behavior we are seeing
> would point at the pool manager not queuing requests and servicing them
> properly when a connection becomes available.
> Watching the activity monitor in SQL2005 we rarely see more than 5-6
> connections in a runnable state. Although there are many more sleeping.
> All the code in the web apps and webservices has been exhaustively gone
> over to ensure connections are being released and in a timely manner.
> What else could we be missing?
>|||A connection will not disappear unless the application terminates the
connection. So, if the application that issued the batch several days ago
is still hanging around without anything to do, it is still going to have a
connection to your server.
You have a problem with your connection pool settings and management. I've
worked on websites that are servicing over 1 million CONCURRENT users with
database requests and they have their pools set to 15.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23MprjJrbGHA.2068@.TK2MSFTNGP02.phx.gbl...
> OK so sleeping processes are only waiting for locks and are actually
> busy....I misready something there. BUT why do some of these show wait
> times of zero when the last batch value is sometimes a day ago? If it
> hasn't run something since yesterday shouldn't the connection be gone by
> now?
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23zJKeBrbGHA.3908@.TK2MSFTNGP02.phx.gbl...
>> We have been fighting with this for some time now. The web group keeps
>> setting the max connections in their connection strings to "2500" yes you
>> read that correct....that seems a little insane. BUT, we've witnesses
>> the site stop working when they lower it. The behavior we are seeing
>> would point at the pool manager not queuing requests and servicing them
>> properly when a connection becomes available.
>> Watching the activity monitor in SQL2005 we rarely see more than 5-6
>> connections in a runnable state. Although there are many more sleeping.
>> All the code in the web apps and webservices has been exhaustively gone
>> over to ensure connections are being released and in a timely manner.
>> What else could we be missing?
>|||"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:O8kKlhrbGHA.1264@.TK2MSFTNGP05.phx.gbl...
>A connection will not disappear unless the application terminates the
>connection. So, if the application that issued the batch several days ago
>is still hanging around without anything to do, it is still going to have a
>connection to your server.
> You have a problem with your connection pool settings and management.
> I've worked on websites that are servicing over 1 million CONCURRENT users
> with database requests and they have their pools set to 15.
This is what I keep hearing...though I haven't heard any ideas on how to
get there. I'm nearly 100% sure we are not leaking connections. But the
webservices choke and quit responding when the pool size is reduced. It
doesn't even seem to queue requests for the timeout period.....just stops
responding altogether.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23MprjJrbGHA.2068@.TK2MSFTNGP02.phx.gbl...
>> OK so sleeping processes are only waiting for locks and are actually
>> busy....I misready something there. BUT why do some of these show wait
>> times of zero when the last batch value is sometimes a day ago? If it
>> hasn't run something since yesterday shouldn't the connection be gone by
>> now?
>>
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23zJKeBrbGHA.3908@.TK2MSFTNGP02.phx.gbl...
>> We have been fighting with this for some time now. The web group keeps
>> setting the max connections in their connection strings to "2500" yes
>> you read that correct....that seems a little insane. BUT, we've
>> witnesses the site stop working when they lower it. The behavior we are
>> seeing would point at the pool manager not queuing requests and
>> servicing them properly when a connection becomes available.
>> Watching the activity monitor in SQL2005 we rarely see more than 5-6
>> connections in a runnable state. Although there are many more sleeping.
>> All the code in the web apps and webservices has been exhaustively gone
>> over to ensure connections are being released and in a timely manner.
>> What else could we be missing?
>>
>|||Is that with authentication being unique per user? Or with a common user
throughout the website? I could certainly believe that with a unique user
authentication scenario but if everyone hitting that site is being connected
with the same credentials then I can't see how 15 connections would ever
service all 1 million people. Call me skeptic, (and new to this) but that
is hard to accept. I knew we had a problem but dang, you make me want to
cry :) .....
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:O8kKlhrbGHA.1264@.TK2MSFTNGP05.phx.gbl...
>A connection will not disappear unless the application terminates the
>connection. So, if the application that issued the batch several days ago
>is still hanging around without anything to do, it is still going to have a
>connection to your server.
> You have a problem with your connection pool settings and management.
> I've worked on websites that are servicing over 1 million CONCURRENT users
> with database requests and they have their pools set to 15.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23MprjJrbGHA.2068@.TK2MSFTNGP02.phx.gbl...
>> OK so sleeping processes are only waiting for locks and are actually
>> busy....I misready something there. BUT why do some of these show wait
>> times of zero when the last batch value is sometimes a day ago? If it
>> hasn't run something since yesterday shouldn't the connection be gone by
>> now?
>>
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23zJKeBrbGHA.3908@.TK2MSFTNGP02.phx.gbl...
>> We have been fighting with this for some time now. The web group keeps
>> setting the max connections in their connection strings to "2500" yes
>> you read that correct....that seems a little insane. BUT, we've
>> witnesses the site stop working when they lower it. The behavior we are
>> seeing would point at the pool manager not queuing requests and
>> servicing them properly when a connection becomes available.
>> Watching the activity monitor in SQL2005 we rarely see more than 5-6
>> connections in a runnable state. Although there are many more sleeping.
>> All the code in the web apps and webservices has been exhaustively gone
>> over to ensure connections are being released and in a timely manner.
>> What else could we be missing?
>>
>sql