Connection Pooling and ADO.NET Providers?

[Carl]

I would be grateful for some explication of the following and/or any
pointers to SDK or internet articles on the topic:

Is it true for all .NET database data providers that connection pooling is
accomplished by utilizing the same connection string?

Where are connections pooled? On the client? On the server? That is, is
the connection pool only on one machine, the client?

Are connection-pooling settings exposed for configuration?

How best does one program to exploit database connection pooling in .NET:
stay as disconnected as possible? close connections as often as possible?

Is there much of an overhead for re-opening or re-connecting to a pooled
connnection subsequently?

Are ADO.NET database connections "unmanged resources"? That is, should they
be part of an IDisposable/finalizer implementation for classes that may have
references to them? What are best practices with regard to finalization,
disposal, and database connections? Other data provider classes such as
datasets?

Thanks very much for your help and advice on this topic.

[Carl]

To elaborate a little, how would one specifically handle the issue of
connection pooling for an application that may have varying connectivity and
RDBMS behind it? It may have SQL Server. It may use ODBC. It may have
Oracle behind.
Is there a single over-weening strategy that can be used, or must one
develop a strategy for each provider? Is connection-pooling behaviour
specific to the provider, the database server, and the access API (that is,
ODBC, etc.)?

Thanks again.

[Angel Saenz-Badillos[MS]]

Carl,
The connection pooling implementation is currently different across the
managed providers. The new providers, SqlClient and OracleClient mp, are
implementing a new managed pooler that is much more performant for managed
code. The wrapper providers, Oledb mp and Odbc mp, do not do any pooling
themselves and rely in the pooling implemented by the native provider they
wrap.

There are some superficial differences like the keywords used, a max pool
size default of 100 for the Sql and Oracle client etc, but in general coding
to take advantage of pooling is always going to be the same. Open your
connection as late as you can, and close it as soon as you can. Make sure
your connection gets closed by using the try finally clause (or the "using"
keyword in c#)

Hope this helped,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Carl" <ple...@donotreplydirectly.co.uk> wrote in message
news:#d5J9ay5...@TK2MSFTNGP11.phx.gbl...

[Paul Clement]

On Mon, 10 Mar 2003 10:59:51 -0600, "Carl" <ple...@donotreplydirectly.co.uk> wrote:

¤ I would be grateful for some explication of the following and/or any

¤ pointers to SDK or internet articles on the topic:
¤
¤ Is it true for all .NET database data providers that connection pooling is
¤ accomplished by utilizing the same connection string?

Yes, for those data providers that support connection pooling.

¤
¤ Where are connections pooled? On the client? On the server? That is, is

¤ the connection pool only on one machine, the client?

Connection pools are created on the machine where the code which creates them is executing. So, that
could be either the client or the server.

¤
¤ Are connection-pooling settings exposed for configuration?

There are several connection pool settings that you may specify in the connection string such as the
maximum or minimum pool size.

¤
¤ How best does one program to exploit database connection pooling in .NET:

¤ stay as disconnected as possible? close connections as often as possible?

Well, it may depend upon the environment in which your application is running, but typically you
open your connection, perform your database operations and then close or dispose it.

¤
¤ Is there much of an overhead for re-opening or re-connecting to a pooled
¤ connnection subsequently?

That's the whole point of using connection pooling - to reduce the overhead by referencing an
existing connection to the database.

¤
¤ Are ADO.NET database connections "unmanged resources"? That is, should they

¤ be part of an IDisposable/finalizer implementation for classes that may have
¤ references to them? What are best practices with regard to finalization,
¤ disposal, and database connections? Other data provider classes such as
¤ datasets?

Use Close or Dispose on the Connection object.

Paul ~~~ pcle...@ameritech.net
Microsoft MVP (Visual Basic)

[Carl]

> ...

> ¤ Is there much of an overhead for re-opening or re-connecting to a pooled
> ¤ connnection subsequently?
>
> That's the whole point of using connection pooling - to reduce the
overhead by referencing an
> existing connection to the database.

I guess what I mean here is the overhead of obtaining a reference to a
connection instance, calling its close/dispose method.
My understanding is that calling close() doesn't destroy the underlying real
connection in the underlying connection pool.

To state another way, I was pondering the overhead of setting, nulling, and
disposing connection object instances repeatedly in managed code, not the
overhead of opening real connections to the data store. I didn't state
things very clearly.

Maybe I should restate this way: What is the most efficient way to handle
referencing and dereferencing connection objects that (presumably) are
aliases to a real, underlying, pooled connection? Can one create and free
references to a connection too much, or is the rule of thumb as simple as
"hold it as little as possible"?

Perhaps I'm still not stating this clearly.

[Carl]

To add to this question, is the connection pool tied to a process?
That is, will the connections hang around for a while after a process ends,
perhaps being available to subsequent launches of the application?
Are connection pools proper to a machine or Windows session?
It sounds like they are created by the client connectivity and are resident
on the client machine?
Is there an underlying client service that runs all the time and keeps the
connection pool in memory across processes?

Thanks again for helping me understand this.

[Paul Clement]

On Mon, 10 Mar 2003 13:44:21 -0600, "Carl" <ple...@donotreplydirectly.co.uk> wrote:

¤ > ...

¤ > ¤ Is there much of an overhead for re-opening or re-connecting to a pooled
¤ > ¤ connnection subsequently?
¤ >
¤ > That's the whole point of using connection pooling - to reduce the
¤ overhead by referencing an
¤ > existing connection to the database.
¤
¤
¤ I guess what I mean here is the overhead of obtaining a reference to a
¤ connection instance, calling its close/dispose method.
¤ My understanding is that calling close() doesn't destroy the underlying real
¤ connection in the underlying connection pool.

Correct. It simply releases the connection to the pool.

¤
¤ To state another way, I was pondering the overhead of setting, nulling, and
¤ disposing connection object instances repeatedly in managed code, not the
¤ overhead of opening real connections to the data store. I didn't state
¤ things very clearly.

No the overhead would be minimal and typical.

¤
¤ Maybe I should restate this way: What is the most efficient way to handle

¤ referencing and dereferencing connection objects that (presumably) are
¤ aliases to a real, underlying, pooled connection? Can one create and free
¤ references to a connection too much, or is the rule of thumb as simple as
¤ "hold it as little as possible"?

As Angel stated: "Open your connection as late as you can, and close it as soon as you can."

This method is relatively routine with respect to connection pooling.

[Paul Clement]

On Mon, 10 Mar 2003 17:06:26 -0600, "Carl" <ple...@donotreplydirectly.co.uk> wrote:

¤ To add to this question, is the connection pool tied to a process?

A connection pool is created per connection string and per process and exclusively used by the
process in which it was created. When the process ends the connection pool is destroyed.

[Carl]

Mr. Clement:

I believe I have seen, and others have reported the following behavior:

1. A connection opened in an application still shows up after the process
has terminated. I guess this would vary in a hosted environment where the
hosting process will outlive the application.

2. Subsequent launches of the same program execute the connection-getting
initialization logic much, much faster than the first time.
After some amount of time, the connection lag seems to be incurred again.

Are these observations mistaken? If not, do they not imply a cross-process
connection pool? How might one test and confirm the behavior of a .NET data
provcider along these lines?

Thanks again for your input on this topic. Connection pooling has always
been a mystery to me and is clearly an oft-misunderstood area.

[Carl]

I guess I have to wonder, too, at the value of connection pooling for most
applications if the connection pool is only process-wide.
I can see the advantage in a hosted environment.
Most non-hosted applications, however, tend to only need one connection. It
would seem that a single connection reference could simply be passed around
within the app (or shared globally in some other way) and this would have
the same effect. Especially if there is no benefit to returning the
connection to the pool
(i.e., a connection is not free on the server, to save resources and save
expensive connection licenses, because it is pooled on the client, and,
because the pool is process-wide only, the connection in the pool is not now
available for other programs on the same machine, as in the case of business
components in the middle tiers.)

Is there a value to connection pooling in a non-hosted .NET (or other)
application? Is connection pooling only useful in contexts like IIS, where
the connection may be pooled in the web server process, and therefore be
available to subsequent instances of the hosted application (domain)?

Thanks again for any clarification on this point. Obviously there is some
key point I am misunderstanding.

[William (Bill) Vaughn]

Connection pools are created when:
1) The process id changes. This means that if you run a Windows forms
application, open a connection, close it (and the connection returns to the
pool) and end the application, the pool should be dropped and the database
connection is actually closed.
2) The connection string changes. This means that if you change UID/PWD
values in the connection string, you get a new pool.
3) The transaction scope changes. If you enlist a connection in a
transaction, the provider creates a new pool.

Connection pools are interesting in cases where you expect to open several
connections over a period of time in a single application. When you're
working with a Windows application, pools permit you to help reduce the
overhead involved in opening a connection. However, a more interesting
scenario is when an ASP or middle tier component instances share a pool.
Since an assembly gets its own pool, all instances of the "application" can
leverage the benefits of previously opened connections.

As far as object disposal, I share the opinions of the other
respondents--open, query, close (quickly).

--
____________________________________
Bill Vaughn
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.
__________________________________

"Carl" <ple...@donotreplydirectly.co.uk> wrote in message

news:#68Yso#5CHA...@TK2MSFTNGP11.phx.gbl...

[Carl]

How does one cause connections to be pooled for ASP and middle tier
components?
Must they be hosted in a process that spans application instances? If so,
what are the candidate hosting environments? IIS and COM+?

It sounds like for a non-hosted desktop application, as long as you maintain
the same connection string, you get about as much efficiency as possible,
but only in one instance of the process. Subsequent launches of the
application have to get a new pool going. This seems to be roughly the same
thing as opening a program-wide connection and passing it around, keeping it
open. Is there any practical difference to passing a reference to the same
Connection instance, or, in effect, passing around a connection string which
is used to get a handle on the same underlying connection?

I guess my main curiosity is the following:
Two non-hosted desktop applications, running on the same machine in
different CLR instances, using the same connection string, will NOT share a
connection pool?

Thanks for all the input on this matter. I'm just trying to clarify, as
I've been quite confused by the documentation.

> > I guess I have to wonder, too, at the value of connection pooling for

[Paul Clement]

On Tue, 11 Mar 2003 10:13:36 -0600, "Carl" <ple...@donotreplydirectly.co.uk> wrote:

¤ Mr. Clement:

¤
¤ I believe I have seen, and others have reported the following behavior:
¤
¤ 1. A connection opened in an application still shows up after the process
¤ has terminated. I guess this would vary in a hosted environment where the
¤ hosting process will outlive the application.
¤
¤ 2. Subsequent launches of the same program execute the connection-getting
¤ initialization logic much, much faster than the first time.
¤ After some amount of time, the connection lag seems to be incurred again.
¤
¤ Are these observations mistaken? If not, do they not imply a cross-process
¤ connection pool? How might one test and confirm the behavior of a .NET data
¤ provcider along these lines?

We're approaching questions now that apply to a specific implementation. I would need to know under
what scenario this is occurring. For example are we dealing with a simple client, IIS, MTS, COM+?
What does the code look like? What database is in use? What driver (e.g. .NET, OLEDB, ODBC) is being
used? If in any of these scenarios the process that created the connection, or a reference to the
connection, remains when it shouldn't that could be a reason why the pool still exists.

I'm afraid I could no more explain the behavior than I could when identifying a UFO. I would need
more information.

[Carl]

How 'bout the following scenario;

A C# console application, compiled in VS.NET, that is a single .exe
assembly. The assembly is not hosted in any way.

The C# application uses the SQLClient provider to connect to a SQL Server
database that is running on a distinct machine on the network.

What should happen in this scenario? The connection pool dies when the app
closes?

To add a couple of questions:

Is there a way to manually create a connection pool machine-wide for a
certain connection string that is not process-dependent? Do the SQL Client
tools not support something like this?

Also, if the connection pool is only process-wide, why is imperative to only
hold a connection reference for short time? Is the connection unavailable
to other requests until you call close()? One connection is allocated for
each Connection object instance?

What happens when two operations on two distinct threads in the same process
try to connect using the same connection string? The first one gets a
connection, it is open until Close() is called, causing the second
connection request to allocate a new connection instance in the pool? That
is, each open connection reference at any one moment in one process is a
distinct database connection?

Any recommended detailed articles on the topic?

Thanks again for tolerating my thick-headed questions.

[Paul Clement]

On Tue, 11 Mar 2003 12:42:47 -0600, "Carl" <ple...@donotreplydirectly.co.uk> wrote:

¤
¤ How does one cause connections to be pooled for ASP and middle tier

¤ components?
¤ Must they be hosted in a process that spans application instances? If so,
¤ what are the candidate hosting environments? IIS and COM+?

Essentially ASP or COM+ applications run under their own surrogate DllHost process which of course
can be shared by multiple clients. ASP applications can also run under the IIS process which would
enable IIS connection pooling.

¤
¤ It sounds like for a non-hosted desktop application, as long as you maintain

¤ the same connection string, you get about as much efficiency as possible,
¤ but only in one instance of the process. Subsequent launches of the
¤ application have to get a new pool going. This seems to be roughly the same
¤ thing as opening a program-wide connection and passing it around, keeping it
¤ open. Is there any practical difference to passing a reference to the same
¤ Connection instance, or, in effect, passing around a connection string which
¤ is used to get a handle on the same underlying connection?

For client-side applications where the connection is created on the client, static connections are
relatively common and there isn't as much of a benefit with respect to connection pooling.

You may want to check the following for more info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

¤
¤ I guess my main curiosity is the following:

¤ Two non-hosted desktop applications, running on the same machine in
¤ different CLR instances, using the same connection string, will NOT share a
¤ connection pool?

If you mean different processes, correct, they will not share a connection pool.

[Carl]

Looks like the following two issues are addressed in the SDK:

"Connections that are not explicitly closed are not added or returned to the
pool."
"Once created, connection pools are not destroyed until the active process
ends."

[Angel Saenz-Badillos[MS]]

Carl,
Great questions, some comments inline.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Carl" <ple...@donotreplydirectly.co.uk> wrote in message

news:eg4j6oA6...@TK2MSFTNGP12.phx.gbl...

> How 'bout the following scenario;
>
> A C# console application, compiled in VS.NET, that is a single .exe
> assembly. The assembly is not hosted in any way

> The C# application uses the SQLClient provider to connect to a SQL Server

> database that is running on a distinct machine on the network.
>
> What should happen in this scenario? The connection pool dies when the
app
> closes?

Yes absolutelly, verify this using server side perfmon or the Sql Profiler,
do not look at the client side performance counters for clr Data since they
have a bug where they accumulate.

> To add a couple of questions:
>
> Is there a way to manually create a connection pool machine-wide for a
> certain connection string that is not process-dependent? Do the SQL
Client
> tools not support something like this?

No, and this is a good thing for security reasons. There would be a bigger
risk for a connection opened under an admin account to be used by a user
account. This type of elevation of priviliges is exactly what hackers are
looking for when trying to break into a system.

> Also, if the connection pool is only process-wide, why is imperative to
only
> hold a connection reference for short time?

think of pooling as a taxi service.A taxi service can carry hundreds of
passengers per day using just a handfull of actual taxis. As long as one
customer is using a taxi nobody else can ride in it, so if one customer
keeps the taxi for the entire day the ammount of passengers per day will
drop significantly.

> Is the connection unavailable
> to other requests until you call close()?

exactly, but the problem is even worse. If you do not call close and let the
connection go out of scope you may run into some stress issues where you are
leaking connections faster than we can clean them up, this forces us to open
new conections and eventually you will run out of connections in the pool
(default of 100), of course there is nothing stopping you from increasing
the max pool size, but a much better solution is to close the connections.

>One connection is allocated for
> each Connection object instance?

For as long as you hold the connection open, with proper coding technique
you will be surprised at how short this time can be. I have seen an
application with nine thousand concurrent users that set its max pool size
to 20 and never came close to running out of pooled connections.

> What happens when two operations on two distinct threads in the same
process
> try to connect using the same connection string?

If they are using the connection at the exact same time they will be using
two distinct database connections. If you have very tight open-execute-close
loops you could have both threads use the same connection like so
T1 open-execute-close
T2 do something else
T1 do something else
T2 open-execute-close //same connection

> The first one gets a
> connection, it is open until Close() is called, causing the second
> connection request to allocate a new connection instance in the pool?
That
> is, each open connection reference at any one moment in one process is a
> distinct database connection?

exactly,

[Pablo Castro [MS]]

A few clarifications:

- Managed connection pools (SqlClient) are per-AppDomain. For regular
applications (i.e. regular WinForms or console apps) this means that the
whole process will have a single pool (unless the process explicitly creates
other AppDomains). For hosted applications that use several AppDomains (i.e.
ASP.NET), there will be one pool for each AppDomain that uses SqlClient.

- Pools never span processes

- Pools die when the AppDomain dies (all AppDomains within a process die
when the process dies)

- Within the AppDomain, there is a pool per-connection-string. If using
integrated security, pools are further partitioned by NT identity (only for
those connection strings with "integrated security=true or SSPI", of
course). If COM+ transactions are involved, then temporary sub-pools are
maintained to keep connections associated with a given transaction together
until the transaction finishes.

HTH

Pablo Castro
Program Manager - Managed Providers

--

This posting is provided "AS IS" with no warranties, and confers no rights.

"Paul Clement" <UseAdddressA...@swspectrum.com> wrote in message
news:6lfs6votj039cc2g8...@4ax.com...

[Carl]

Mr. Castro,

Thanks very much for the clarification. That's very useful information.

Is there an MSDN or other article detailing pooling in this way with managed
providers?

I am curious about the recommended strategy of "stirring the [connection]
pool" - does this practice apply in a .NET app?
Should one keep a global reference to a connection to keep the pool going?
Doesn't this tie up at least one connection? Should one then set the
minimum pool size to 2 instead of zero?

So, I guess passing connections between AppDomains is not feasible? (Just
curious on that point - I know it's a bad idea.)
I wonder what happens if you try to pass an ADO connection between a COM
client and an out-of-process ActiveX executable?

Thanks again for the very good information.

"Pablo Castro [MS]" <pabl...@online.microsoft.com> wrote in message
news:eUQYWJR6...@TK2MSFTNGP10.phx.gbl...

> >

[William (Bill) Vaughn]

Pablo,
My tests show that AppDomain pools do NOT die when the AppDomain dies. I
see these pools hanging on until IIS is restarted. They remain even if the
connected server dies. This was just retested on a Windows XP Pro "server"
and .NET 2003 RC3.
Comments?

--
____________________________________
Bill Vaughn
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.

__________________________________

"Pablo Castro [MS]" <pabl...@online.microsoft.com> wrote in message
news:eUQYWJR6...@TK2MSFTNGP10.phx.gbl...

[Carl]

How exactly would one definitively test this? With performance monitors?

"William (Bill) Vaughn" <billvaRe...@nwlink.com> wrote in message
news:e2NpCYa6...@TK2MSFTNGP09.phx.gbl...

[William (Bill) Vaughn]

Yes, I based these tests on Perfmon and performance counters (2003 versions)
which are supposed to be "fixed". I heard that these perfmon controls had
issues in the early framework versions.

--
____________________________________
Bill Vaughn
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.
__________________________________

"Carl" <ple...@donotreplydirectly.co.uk> wrote in message
news:evH$sBb6CH...@TK2MSFTNGP10.phx.gbl...

[Angel Saenz-Badillos[MS]]

Bill Vaughn,

The performance counters are not fixed for the Everett release, they have
the exact same accumulation problem described in
http://support.microsoft.com/default.aspx?scid=kb;en-us;314429

If you are seeing a situation where you have a pooled connection in an
appdomain, shut down the appdomain and the connection does not go away on
the server then this is a bug.

Thank you,

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"William (Bill) Vaughn" <billvaRe...@nwlink.com> wrote in message

news:u6wd2ub6...@TK2MSFTNGP11.phx.gbl...

[Pablo Castro [MS]]

You probably used the "Data" perf monitors. Those counters aren't properly
reset; that's why you see that the pools are not going away.

This is a known issue and it's documented here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314429

Pablo

--
This posting is provided "AS IS" with no warranties, and confers no rights.

"William (Bill) Vaughn" <billvaRe...@nwlink.com> wrote in message
news:e2NpCYa6...@TK2MSFTNGP09.phx.gbl...