connection pooling

[John A Grandy]

My understanding of ADO.NET connection pooling as it relates to Sql Server
is that once a connection is created and opened, behind the scenes ADO.NET
adds this connection to a pool of connections with identical connection
strings. If a connection is closed in code this does not necessarily mean
that the connection is closed in the pool. Connections in the pool stay
open based on some other set of parameters which I don't remember much about
(neither the exact config settings, nor where the config settings are
stored).

I do remember that connections in the pool are gradually dropped off
(perhaps down to a lower threshold) due to lack of use ... however , the key
point is that in a highly scalable app such as a web app , opening a
connection in code does not necessarily incur the total overhead. On
opening a SqlConnection that has a connection string identical to an
available connection in the pool with an identical connection string, the
overhead is extremely low.

Is the above generally true ? If so, where are the connection pooling
config settings stored ? And how have things changed between SS2K and SS05
?

 

[William \(Bill\) Vaughn]

Basically, your description is true. There is no way to change the length of
time a "closed" connection remains in the pool waiting to be reused before
it's dropped. The amount of time is 4-8 minutes. While there is a setting
that implies that this timeout can be changed, it applies to clustered
servers (only). There are no interfaces to other settings like the size of
the pool etc--they are set when the connection is first opened (in the
ConnectionString). While opening a pooled connection is cheap, it's not
free. There is a certain amount of overhead added to the first use and
subsequent of a connection (even one that's been left in the pool for
reuse).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik [MVP C#]]

John -

ConnPools are -
per connection string/per appdomain/per process/per NT Identity.

You can tweak their settings @ the connection string in case of SqlClient.

Between SS2K & 2K5, there are a number of improvements under the scenes. But
on the outside, there are static methods available to clear connection pools
in case they are corrupted.

Hope that helps.