connection
Connection Pooling for the OLE DB .NET Data Provider
This is preliminary documentation and subject to change.
Send feedback on this topic.
The OLE DB .NET Data Provider automatically pools connections using OLE DB session pooling. Connection string arguments can be used to enable or disable OLE DB services including pooling. For example, the following connection string will disable OLE DB session pooling and automatic transaction enlistment.
Provider=SQLOLEDB;OLE DB Services=-4;Data Source=localhost;Integrated Security=SSPI;
For more information about OLE DB session pooling or resource pooling, as well as disabling pooling by overriding OLE DB provider service defaults, see the OLE DB Programmer's Reference in the MSDN library located at http://msdn.microsoft.com/library.
CAUTION You must always close the Connection when you are finished using it. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed are not added or returned to the pool.
Connection Pooling for the SQL Server .NET Data Provider
This is preliminary documentation and subject to change.
Send feedback on this topic.
The SQL Server .NET Data Provider relies on Windows 2000 Component Services to provide connection pooling using an implicit pooling model by default. The SqlConnection object also has several connection string modifiers that you can set to control implicit pooling behavior.
Pool Creation and Assignment
Each connection pool is associated with one distinct connection string, using an exact matching algorithm. If no exact match is found, 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 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 connection strings differ.
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Integrated Security=SSPI;Initial Catalog=northwind";
conn.Open();
// Uses 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 be currently unused, have a matching transaction context or not be associated with any transaction context, and have a valid link to the server.
When the maximum pool size is reached, the request is queued. The object pooler satisfies these requests by reallocating connections as they are released back into the pool. If the time-out period (determined by the Connect Timeout connection string property) elapses before a connection object can be obtained, an error occurs.
CAUTION You must always close the Connection when you are finished using it. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed are not added or returned to the pool.
Connection Removal
The object 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 object 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.
It is possible for an apparently valid connection to be drawn from the pool although the associated server has disappeared. When this occurs, an exception is generated. However, the user must still close the connection 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, each containing 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 some additional connection string key/value pairs that can be used to adjust the behavior of the connection pooling logic.
The following table describes ConnectionString values that 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 never time out.
Connection Reset 'true' Determines whether the database connection is reset when being removed from the pool. 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 SQLConnection object is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool.