Re: Connection pooling in SQL 2000


<ipramod@xxxxxxxxx> wrote in message
news:1165495041.239452.249350@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

I have some questions to be asked related to Connection pooling....

1)

I have enabled connection pooling and created a single connection to
SQL Server 2000 (SQLServer:General Statistics::User Connections = 1).
If I close the connection then the connection is placed back into the
pool (SQLServer:General Statistics::User Connections = 0) ? or the
value of the performance counter SQLServer:General Statistics::User
Connections remains 1 ?

2)

Suppose, I have enabled connection pooling and created a single
connection to SQL Server 2000. I closed the first connection and
immediately open another connection with the same connection string.
So, the first connection without being released is used or the new
connection is used from the connection pool?

3)

Suppose, I have enabled connection pooling and created a single
connection to SQL Server 2000. Now, immediately open another connection
with the same connection string. So, the first connection is used or
the new connection is used from the connection pool?

Please let me know ASAP.


[Just a suggestion: Don't even hint at 'demanding' an answer from newsgroup
participants, ie "let me know ASAP". Most people are willingly giving their
time to help others because they like to, but suggesting they must do so,
and do so promptly, tends to annoy. <g>]

The general answer to your questions is that you are essentially correct on
how ADO pooling should work, but in the 'Real World' results may not be
quite so definitive.

The whole point of connection pooling is have the ability to provide a
reuseable connection on demand instead of going the complex route of
creating a new one everytime one is needed. (Duh. <g>) So with that as a
pool's main objective you can see that if a pool could be deterministically
controlled then the pool loses 'free will'. <g> It no longer is a 'pool' it
is just a static collection.

So when you request a connection as in your above examples - the simple
answer is you will get 'A Connection' - which one, whether one already
there, or a new one - who knows? Who cares? You will get one.

Also appreciate that connection pooling is a complicated dance between the
data access library - the provider (not all pools are exactly alike) - and
the database engine (it may have its own 'connection pooling' going on).
Other factors may often be in play, eg. some queries while appearing to use
only one connection in the code, may actually cause the database to create
several 'connections' to honor the request. A simple client/server scenario
in a development environment may appear to obey an orderly sequence of
events, which quickly decays in a 'real world' multiple user, heavy-loaded
production environment.

hth
-ralph





.

Relevant Pages

  • Re: Connection Pool Issue
    ... Max pool size 10 seems low... ... My first thought would be that connection poolling is actually disabled on A ... Server A and Server B both are running Windows 2000 Server sp4, ... all the usual places for enabling connection pooling. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: ado connection object in vb6
    ... "Global connection objects are bad in so many ways. ... The key is that ADO uses 'connection pooling'. ... "Pooling in the Microsoft Data Access Components" ...
    (microsoft.public.vb.database.ado)
  • Re: "General Network Error" and Connection Pooling
    ... connection is cleared but any remaining connections are not. ... getting exceptions until the pool is cleared. ... > However, if i disable connection pooling, I get no error. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: connection pooling
    ... the architecture that determines whether you need connection pooling. ... it's often suggested that you build a separate pool for each ... >>> A web server has a finite capacity. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: pooled connection myth
    ... Each time a connection is closed it is gone. ... You are making a rather large assumption: that the connection object you ... obtain from the pool is an instance provided by the driver you ... This is connection pooling. ...
    (comp.lang.java.programmer)