. NET Framework SQL Server data provider connection pool _mssql

You can also provide several connection string modifiers to control connection pooling behavior, see the section "Controlling connection pooling using connection string keywords" later in this topic.

Creation and allocation of pools

When the connection is opened, a connection pool is created based on an exact matching algorithm that 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 opened, a new pool is created if the connection string does not exactly match an existing pool.

In the following example, three new SqlConnection objects are created, but only two connection pools are required to manage these objects. Note that the difference between the first and second connection strings is the Initial Catalog value assigned.

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 terminates. The maintenance of inactive or empty pools requires minimal overhead.

Add to Connection

The connection pool is created for each unique connection string. When 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, the object is fetched from the pool if there is a connection available. To be an available connection, the connection must currently not be used, has a matching transaction context, is not associated with any transaction context, and has a valid link to the server.

If the maximum pool size has been reached and no available connections exist, the request will be queued. When the connection is released back into the pool, the connection pool manager satisfies the requests by reallocating the connection. When you call Close or Dispose on Connection, the connection is freed back into the pool.

caution It is recommended that you always turn off Connection after you have finished using it so that the connection can be returned to the pool. This can be accomplished 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 is out of range but not explicitly closed, the connection is returned to the connection pool only if the maximum pool size is reached and the connection is still valid.

Note Do not call Close or Dispose on Connection, DataReader, or any other managed object in the Finalize method of the class. In finalizers, only unmanaged resources that are directly owned by the class are freed. If the class does not own any unmanaged resources, do not include the Finalize method in the class definition. For more information, see garbage collection programming .

Removal of connections

If the connection lifetime has expired or the connection pool Manager detects that a connection to the server has been disconnected, the connection pooling manager removes the connection from the pool. Note that this can only be detected if you are trying to communicate with the server. If a connection is found to be 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 into the pool and marked as invalid. Once found, these connections will be permanently removed.

If there is a connection to a server that has disappeared, it is possible to remove the connection from the pool even if the connection pool manager does not detect the disconnected connection and marks it as invalid. When this occurs, an exception is generated. However, in order to release the connection back into the pool, it must still be closed.

Transaction support

The connection is taken out of the pool and allocated according to the transaction context. The context of the requesting thread and the assigned connection must match. Therefore, each connection pool is actually divided into connections that do not have an associated transaction context and N A child part that contains a connection to a particular transaction context.

When the connection is closed, it is released back into the pool and placed in the appropriate sub section according to its transaction context. Therefore, even if a distributed transaction is still suspended, you can still close the connection without generating an error. This allows you to commit or abort a distributed transaction later.

Controlling connection pooling using connection string keywords

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

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

name	Default Value	Description
Connection Lifetime	0	When a connection is returned to the pool, its creation time and current time are compared, and if the interval exceeds the value specified by Connection Lifetime (in seconds), the connection is destroyed. It can be used in a clustered configuration to force load balancing between the running server and the server that is just online. A value of 0 (0) will cause the pool connection to have the maximum time-out period.
Connection Reset	' True '	Determines whether a database connection is reset when it is removed from the pool. If set to False for Microsoft SQL Server version 7.0, you will avoid an extra round trip while getting the connection, but it is important to note that the connection state, such as the database context, is not reset.
Enlist	' True '	When True, if a transaction context exists, the pool manager automatically enlists 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 '	When True, the connection is removed from the appropriate pool, or the connection is created and added to the appropriate pool if necessary.

Performance counters for connection pooling

The SQL Server. NET Framework Data Provider adds several performance counters that enable you to fine-tune connection pool characteristics, detect intermittent problems associated with failed connection attempts, and detect issues related to timeout requests for SQL Server.

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

counter	description
sqlclient:current # Pooled and non Pooled connections
sqlclient:current # Pooled connections	The number of connections in all current pools that are associated with a particular process.
sqlclient:current # Connection Pools
sqlclient:peak # Pooled connections	from Peak number of connections in all pools since the start of a particular process. Note that this counter is only available if it is associated with a particular process instance. The _global instance always returns 0.
sqlclient:total # failed connects	open even The total number of failed attempts for any reason.

note     when using the SQL Server. NET Framework Data Provider performance counters with the ASP.net application, only the _global real Examples are available. Therefore, the value returned by the performance counter is the sum of the counter values for all asp.net applications.