Connection Pooling

Question

I have some doubts related to connection pooling. In SQL Server Connection Pooling article it was mentioned like " When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity."

Now i have my own windows form application which has SQL connection.

1. So when I open application the SQL connection open for first time and a pool is created. So if i close the application does pool gets destroyed automatically or will it be exists even after application is closed?

2. If I open the application again after some time does the connection drawn from the existing pool if already exists or not?(but it is mentioned like pool is per process)

3. There exists connection timeout for a connection. So is there any timeout for a pool too?

Answer 1

1. Pools are destroyed if there is no activity for a specified amount of time. More details on inactivity time can be found here. I believe this will answer #3 as well. However, if you specify the MinPoolSize in the connection string, it is destroyed when AppDomain is unloaded and process ends.

2. A pool is specific to process so each time a pool will be created.

Answer 2

The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed. Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed.

If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown

refer this link Connection Pooling remove connections section

Answer 3

1) as you quoted the pool is pooled per process, so if you close your application it closes the pool.

2) Does not matter as the pool is per process, when you close the application it closes the pool.

3) Yes, it is set in the connection string with the key Connection Lifetime or Load Balance Timeout.

Connection Lifetime -or- Load Balance Timeout

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online. A value of zero (0) causes pooled connections to have the maximum connection timeout.

If you do not use the above setting (it defaults to 0) it will just use the default behavior of the pool

The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed.

Answer 4

Connection pools are in the end nothing magical. Think of them as .NET objects that simply keep a list of connections.

As such these objects exist per AppDomain and live inside the CLR, which is bound to the lifetime of your process.

If your process goes away, so does the CLR instance, so do the AppDomains and so do the connection pools. Just like any other .NET object you had.

If you restart your application the connection pools get recreated, again based on the rules described by MSDN.