SQL Server. NET Framework Data Provider connection pool

server| Program | data

A friend suggested that I control the connection pool connection, said to improve performance, but read the following article, the sense of the connection pool is maintained by the system itself, the program does not need to be controlled. Do you have any comments? Is there a way to raise performance on connection issues?
SQL Server. NET Framework Data Provider connection pool
Pool connectivity can significantly improve the performance and scalability of your application. The SQL Server. NET Framework Data Provider automatically provides connection pooling for ado.net client applications. 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 value assigned to the 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 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, it is compared to its creation time and the current time, and if the interval exceeds the value specified by the 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 the 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 the 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.
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 the number of current pool or non pool connections.
Sqlclient:current # Pooled connections The number of connections that are associated with a particular process in all current pools.
Sqlclient:current # Connection Pools The number of pools currently associated with a particular process.
Sqlclient:peak # Pooled Connections The 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 the total number of times the attempt to open the connection failed for any reason.

Note When you use the SQL Server. NET Framework Data Provider performance counters with the ASP.net application, only the _global instance is available. Therefore, the value returned by the performance counter is the sum of the counter values for all asp.net applications.