Connection Pooling for the .NET Framework Data Provider for SQL Server

Article
10/17/2014

Pooling connections can significantly enhance the performance and scalability of your application. The .NET Framework Data Provider for SQL Server provides connection pooling automatically for your ADO.NET client application. You can also supply several connection string modifiers to control connection pooling behavior (see the section "Controlling Connection Pooling with Connection String Keywords" later in this topic).

Pool Creation and Assignment

When a connection is 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.

In the following example, three new SqlConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ by the value assigned for Initial Catalog.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();      
// Pool A is created.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=pubs";
conn.Open();      
// Pool B is created because the connection strings differ.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();      
// The connection string matches pool A.

Once created, connection pools are not destroyed until the active process ends. Maintenance of inactive or empty pools involves minimal system overhead.

Connection Addition

A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size.

When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, the connection must currently be unused, have a matching transaction context or not be associated with any transaction context, and have a valid link to the server.

If the maximum pool size has been reached and no usable connection is available, the request is queued. The connection pooler satisfies these requests by reallocating connections as they are released back into the pool. Connections are released back into the pool when you call Close or Dispose on the Connection.

**CAUTION **It is recommended that you always close the Connection when you are finished using it in order for the connection to be returned to the pool. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

Note Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Programming for Garbage Collection.

Connection Removal

The connection pooler will remove a connection from the pool if the connection lifetime has expired, or if the pooler detects that the connection with the server has been severed. Note that this 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. The connection pooler periodically scans connection pools looking for objects that have been released to the pool and are marked as invalid. These connections are then permanently removed.

If a connection exists to a server that has disappeared, it is possible for this connection to be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. When this occurs, an exception is generated. However, you must still close the connection in order to release it back into the pool.

Transaction Support

Connections are drawn from the pool and assigned based on transaction context. The context of the requesting thread and the assigned connection must match. Therefore, each connection pool is actually subdivided into connections with no transaction context associated with them, and into N subdivisions that each contain connections with a particular transaction context.

When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. This allows you to commit or abort the distributed transaction at a later time.

Controlling Connection Pooling with Connection String Keywords

The ConnectionString property of the SqlConnection object supports connection string key/value pairs that can be used to adjust the behavior of the connection pooling logic.

The following table describes the ConnectionString values you can use to adjust connection pooling behavior.

Name	Default	Description
Connection Lifetime	0	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) will cause pooled connections to have the maximum time-out.
Connection Reset	'true'	Determines whether the database connection is reset when being removed from the pool. For Microsoft SQL Server version 7.0, setting to false avoids making an additional server round trip when obtaining a connection, but you must be aware that the connection state, such as database context, is not being reset.
Enlist	'true'	When true, the pooler automatically enlists the connection in the current transaction context of the creation thread if a transaction context exists.
Max Pool Size	100	The maximum number of connections allowed in the pool.
Min Pool Size	0	The minimum number of connections maintained in the pool.
Pooling	'true'	When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool.

Performance Counters for Connection Pooling

The .NET Framework Data Provider for SQL Server adds several performance counters that enable you to fine-tune connection pooling characteristics, detect intermittent problems related to failed connection attempts, and detect problems related to timed-out requests to your SQL Server.

The following table lists the connection pooling counters that can be accessed in Performance Monitor under the ".NET CLR Data" performance object.

Counter	Description
SqlClient: Current # of pooled and non pooled connections	Current number of connections, pooled or not.
SqlClient: Current # pooled connections	Current number of connections in all pools associated with the process.
SqlClient: Current # connection pools	Current number of pools associated with the process.
SqlClient: Peak # pooled connections	The highest number of connections in all pools since the process started. Note: this counter is only available when associated with a specific process instance. The _Global instance will always return 0.
SqlClient: Total # failed connects	The total number of connection open attempts that have failed for any reason.

Note When using the .NET Framework Data Provider for SQL Server performance counters in conjunction with ASP.NET applications, only the _Global instance is available. As a result, the value returned by the performance counter is the sum of the counter values for all ASP.NET applications.