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...
>|||"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...
>|||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...
>

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

Pooling Connection Object

Hello:

How does one pool a connection object? I have the same application running
on 4 different machines, all connecting to the same server/SQL Server 2000
instance for DB activity.

Some posts have mentioned pooling the connection objects to reduce overhead,
but how do I do that for the 4 separate computers.

Appreciate any response.

Regards,

Ryan Kennedy
Ryan P. Kennedy wrote:

> Hello:
> How does one pool a connection object? I have the same application running
> on 4 different machines, all connecting to the same server/SQL Server 2000
> instance for DB activity.
> Some posts have mentioned pooling the connection objects to reduce overhead,
> but how do I do that for the 4 separate computers.
> Appreciate any response.

You would want to connect your four applications to a single middle tier
process, which remained running, and which maintained one or more open
connections to the DBMS. The middle tier process would act as a proxy for
your client applications, receiving requests, and sending them to the
DBMS via a perpetually-kept connection, and then relaying the returns from
the DBMS to the clients.
A pooled connection can only live as long as the client that made it.
There are two main savings from pooled connections: They save the typically
longer time it takes to make a new connection, and they can be used to
limit the number of simultaneously open DBMS connections, in cases where
licensing or performance dictate that you cannot make a separate connection
for each client.
Joe Weinstein at BEA
> Regards,
>
> Ryan Kennedy

Pooling

Im using SQL Express and ADO.

The connection string im using is

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mydatabase;Data Source=TESTBOX\SQLEXPRESS"

The problem i have is there are lots of alerts generated in the security evenlog which are login events almost every second. Our app has many different threads and processes accesing the same database in a Queue fashion. What im concerned by looking at these logs and also looking at the connections opened is that i see all the connections open and close every second and so no connection pooling is happening.

Am i missing something ?

The connection pool is security context specific. When you are using integrated security, each individual user will have their own connection pool.

It is not unusual for a heavily used production server to generate hundreds of login alerts (maybe even thousands) per second. If appropriate for your situation, you may choose to change the alerts to log only Login Failures (and ignore Login successes. )

|||

Thanks for the reply. The services are the ones doing all the calls into SQL. It is always only one user ( The service is running as this user) And i dont see any connection being active for more than a few ms. And i think that means that the connection pool is not even getting created . no ?

but if connection pooling is happening then how can see thousands of logins ? is it not supposed to use the existing connection in the pool and not relogin to SQL ?

Arnie Rowland wrote:

The connection pool is security context specific. When you are using integrated security, each individual user will have their own connection pool.

It is not unusual for a heavily used production server to generate hundreds of login alerts (maybe even thousands) per second. If appropriate for your situation, you may choose to change the alerts to log only Login Failures (and ignore Login successes. )

|||

Having the Login Success/Failure events write to Event Log has little direct relationship with the connection. It has everything to do with security.

One connection pool will still generate an Event Log entry every time there is a query from any user. Every incoming query will have the users' access and/or permissions validated.

If you don't want to have Login Successes filling up your Event Log, then you can change that to Log only Failures. -Or not at all.

|||

ok. but if i set the username password as userid=sa , password then i dont see any events.

btw how do i even see if connection pool is being created or not ?

|||

Take look at this site

http://support.microsoft.com/default.aspx/kb/166083

|||

To determine if connection pooling is happening in the ADO/OLEDB you can enable tracing using the following whitepaper: http://msdn2.microsoft.com/en-us/library/aa964124.aspx.From your connection string you’re using SQLOLEDB which will require that you’re at least on MDAC 2.8 SP2 to get complete tracing.

Hope this helps.

|||Use SQL Profiler and see if you get sp_reset_connection calls. If you do, then you have connection pooling working, otherwise you will see new connections.

You might be destroying the pool if you do not hold onto at least one connection.
There is a performance optimization for COM+ and IIS scenarios, but for a standalone application make sure you do not release everything. For instance in the following ADO code:

For i = 1 To 100

c.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;OLE DB Services=-1; Data Source=.; Initial Catalog=pubs;"

Set r = New ADODB.Recordset

r.Open "SELECT * FROM Authors", c

Set r = Nothing

c.Close

Set c = Nothing

Next I

As soon as you set c to Nothing and don't have other active connections you are going to destroy the whole oledb session pool.