How does ADO.Net connection pooling work?

A connection to a database is a resource intensive operation that typically comprises a few steps. A channel of communication between the application and the database should be established before the initial handshake with the database server can happen. This channel can either be a socket or a named pipe. Once this connection is established and the initial handshake with the server succeeds, the connection metadata (connection string information to connect to the database) is parsed and the connection to the database is authenticated by the database server.

ADO.Net has for long been one of the most popular data access frameworks. Connection pooling is a feature in ADO.Net that enables you to improve data access performance in your application.

What is connection pooling?

Connection pooling is a strategy that is used to minimize the cost associated with opening and closing connections to the database. It enables you to reuse connections rather than create new connections to the database as and when they are requested.

A connection pool contains a collection of idle, open, reusable connections. The pooler manages the connection pool and maintains a set of active connections for each given connection metadata, connection configuration information.

Note that you can have multiple connection pools exist in the same application domain. A connection pool is created per unique connection string that you use to connect to the database and not per database. So, the first time you connect to a database with a particular connection string, a new connection pool is created. When you connect to the same database with the same connection string the next time, no new connection pool is created, rather, the existing connection pool is reused.

It should be noted that when you are using Integrated Security, a connection pool would be created for each user that accesses the client system, i.e., the connections are pooled per Windows Identity. In this regard, the MSDN states: “When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created.”

Configuring connection pooling

How do you control connection pooling behavior? Well, you can manage connection pools using certain keywords in your connection string. The important ones include the following:

• ConnectionTimeout — this is used to specify the wait period (in seconds) when a new database connection is requested for. The default value is 15.
• MinPoolSize — this represents the minimum number of connections in the pool.
• MaxPoolSize — this represents the maximum number of connections in the pool. The default value is 100.
• Pooling — this controls if connection pooling is turned on or off and can have a value of true of false. When this is set to true, the requested connection is retrieved from the appropriate connection pool.

The following code snippet illustrates how connection pool can be configured using connection string:

string connectionString="Data Source=localhost;Initial Catalog=Sample; Integrated security=SSPI;Connection Timeout=30; Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;";

using (SqlConnection connection = new SqlConnection(connectionString))

{

   connection.Open();

   //Write code here to perform CRUD operations on the Sample database

}

You can monitor connection pooling behavior by executing the sp_who or sp_who2 stored procedures in SQL Server. You can also use the SQL Server Profiler or take advantage of Performance Counters to monitor connection pooling behavior.

There are a few potential problems with connection pooling. One such issue is pool fragmentation. When working with connection pooling, you should be aware of pool fragmentation and ensure that proper measures (your architecture should be designed to address pool fragmentation mitigation strategies) are adopted to avoid pool fragmentation. Note that pool fragmentation might occur if you are using Integrated Security or even when your application is using too many connection pools.

In order to ensure that connection pooling works efficiently, you should ensure that you close the database connections when they are no longer needed. You should never call the Close or Dispose method on a Connection instance in the Finalize method. You should also close the transaction instances before the related connection objects are closed or disposed. Here’s a nice read on this topic.