. NET Framework SQL Server data provider 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 a connection is opened, a connection pool is created based on an exact matching algorithm, which associates the connection pool with the connection string. 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 new SqlConnection objects are created, 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 A.

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 a SqlConnection object is requested, 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. When you call Close or Dispose for Connection, the Connection is released back to the pool.

Warning it is recommended that you always disable the Connection after the Connection is used, so that the Connection can be returned to the pool. This can be achieved using the Close or Dispose method of the Connection object. 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.

Do not call Close or Dispose for Connection, DataReader, or any other managed object in the Finalize method of the class. 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 include the Finalize method in the class definition. 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 andNThe 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

The ConnectionString attribute of the SqlConnection object supports connection string key/value pairs, which can be used to adjust the logic of the connection pool.

The following table describes the ConnectionString values that can be used to adjust the connection pool behavior.

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 time interval exceeds the value specified by Connection Lifetime (in seconds ), the connection is destroyed. 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 to false, it 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'	If true, 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'	If the value is true, the connection is retrieved from the corresponding pool, or the connection is created and added to the corresponding pool when necessary.

Performance counters of the Connection Pool

SQL Server. the NET Framework data provider adds several performance counters, which enable you to fine-tune the connection pool features and detect intermittent problems related to failed connection attempts, it also detects issues related to SQL Server timeout requests.

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	The number of connections associated with a specific process in all the current pools.
SqlClient: Current # connection pools	The 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 always returns 0.
SqlClient: Total # failed connects	The total number of attempts to open the connection that failed for any reason.

Note that only the _ Global instance is available when you use the SQL Server. NET Framework data provider performance counters with ASP. NET applications. Therefore, the value returned by the performance counter is the sum of the counter values of all ASP. NET applications.