Introduction to ADO. NET database connection pool

Extracted from msdn:

Establishing pool connections can significantly improve applicationsProgramPerformance and scalability. The SQL Server. NET Framework data provider is automatically named ADO. net.
The client application provides a connection pool. You can also provide several connection string modifiers to control connection pool behavior. For more information, see "using connection string keywords to control connection pool" in this topic.

Pool creation and allocation

When the connection is opened, it will be based on a precise matchAlgorithmTo create a connection pool. This algorithm associates the connection pool with the strings in the connection. Each connection pool is associated with a different connection string. When a new connection is enabled, if the connection string does not match the existing pool accurately, a new pool is created.

In the following example, three newSqlconnection
Object, but you only need to use two connection pools to manage these objects. Note that the difference between the first and second connection strings isInitial
CatalogAssigned value.

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.
 

Once the connection pool is created, it will not be destroyed until the active process is terminated. Maintenance of inactive or empty pools only requires minimal system overhead.

Add connection

The connection pool is created for each unique connection string. After a pool is created, multiple connection objects are created and added to the pool to meet the minimum pool size requirements. The connection is added to the pool as needed until the maximum pool size is reached.

When the requestSqlconnection
If a connection is available, the object is obtained from the pool. To become an available connection, the connection must not be used currently. It has a matched transaction context or is not associated with any transaction context, and has a valid link with the server.

If the maximum pool size is reached and no available connections exist, the request will be queued. When a connection is released back to the pool, the connection pool manager reallocates the connection to meet these requests. Pair
ConnectionCallCloseOrDisposeThe connection is released back to the pool.

WarningRecommendedConnection
Always close the connection so that the connection can be returned to the pool. This can be usedConnectionObjectCloseOrDispose
Method. Connections that are not explicitly closed may not be added or returned to the pool. For example, if the connection has exceeded the range but is not explicitly closed, the connection will be returned to the connection pool only when the maximum pool size is reached and the connection is still valid.

Note:Do notFinalizeMethod
Connection,DatareaderOr any other managed object callCloseOr
Dispose. In the Terminator, only the unmanaged resources directly owned by the class are released. If the class does not have any unmanaged resources, do not includeFinalize
Method. For more information, see garbage collection programming.

Remove Connection

If the connection life has expired, or the connection pool Manager detects that the connection has been disconnected from the server, the connection pool manager will remove the connection from the pool. Note that this situation can be detected only when you try to communicate with the server. If a connection is no longer connected to the server, it is marked as invalid. The connection pool manager periodically scans the connection pool to find objects that have been released to the pool and marked as invalid objects. The connections are permanently removed.

If a connection exists with the server that has been removed, the connection pool manager may still remove the connection from the pool even if it does not detect a disconnected connection and marks it as invalid. In this case, an exception is generated. However, to release the connection back to the pool, you must disable it.

Transaction support

The connection is retrieved from the pool and allocated based on the transaction context. The context of the request thread and the allocated connection must match. Therefore, each connection pool is actually divided into connections with no associated transaction context and
NThe sub-parts that each contain a connection to a specific transaction context.

When the connection is closed, it will be released back to the pool and placed into the corresponding sub-parts according to the transaction context. Therefore, even if the distributed transaction is still suspended, you can close the connection without generating an error. In this way, you can submit or stop distributed transactions later.

Use the connection string keyword to control the connection pool

SqlconnectionObjectConnectionstring
Properties support connecting string key/value pairs. These key/value pairs can be used to adjust the behavior of the Connection Pool logic.

The following table describesConnectionstringValue.

Name	Default Value	Description
Connection lifetime	0	When the connection is returned to the pool, the creation time of the connection is compared with the current time. If the interval exceedsConnection lifetime The specified value (in seconds) destroys the connection. In clustering configuration, you can use it to force load balancing between the running server and the online server. If the value is zero (0), the pool connection has the maximum timeout period.
Connection Reset	'True'	Determine whether to reset the database connection when removing it from the pool. For Microsoft SQL Server 7.0, if it is set FalseIt will avoid an additional round-trip process when obtaining the connection, but it must be noted that the connection status (such as the database context) will not be reset.
Enlist	'True'	WhenTrueIf the transaction context exists, the pool manager automatically registers the connection in the current transaction context of the Creation thread.
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'	WhenTrue The connection will be retrieved from the corresponding pool, or the connection will be created and added to the corresponding pool when necessary.

Performance counters of the Connection Pool

SQL Server. NET Framework
The data provider adds several performance counters that enable you to fine-tune the connection pool features, detect intermittent problems related to failed connection attempts, and detect and
Timeout request related issues.

The following table lists the connection pool counters that can be accessed in the Performance Monitor under the ". Net CLR data" performance object.

counter	description
sqlclient: Current # pooled and non pooled connections	Number of current pool connections or non-pool connections.
sqlclient: Current # pooled connections	Number of connections associated with a specific process in all the current pools.
sqlclient: Current # connection pools	Number of pools currently associated with a specific process.
sqlclient: peak # pooled connections	the peak number of connections in all pools since the start of a specific process. Note: This counter is available only when it is associated with a specific process instance. _ global the instance always returns 0.
sqlclient: Total # failed connects	total number of attempts to open the connection failed for any reason.

note . net Framework
data provider performance counters and ASP. only _ global instances are available. Therefore, the value returned by the performance counter is the sum of the counter values of all ASP. NET
applications.