Analysis of ADO. NET data connection pool

This article originated from discussing the concept of connection pool with C # MVP Zhang Xiang during gcr mvp Open Day. Therefore, write an article to analyze the connection pool.

Why connection pool?
The first question to analyze a technology is why it exists.
Each connection to SQL Server requires three handshakes of TCP/IP protocol, identity authentication, connection establishment in SQL Server, and resource allocation. When the client closes the connection, the client terminates the physical connection with SQL Server. However, people who have worked in Database Development know that it is normal to close the connection after each operation, an application needs to constantly connect to SQL Server and close the connection even when the load is low. The same application may have multiple connections at the same time.

Therefore, it is a waste of resources to establish and close connections in this way. Therefore, Ado.net has a connection pool mechanism. Maintain the connection pool in the application process of the SQL Server Client. Manage the connections between Ado.net and SQL Server in a unified manner, that is, the Connection pool maintains a Connection with SQL Server when Connection. when Open () is enabled, only one Connection established with SQL Server is allocated from the Connection pool. close () does not physically disconnect from SQL Server, but only recycles the connection.

Therefore, the connection pool can always maintain a certain number of connections to SQL Server, so that applications can use these connections repeatedly to reduce performance loss.

Reset sys. sp_reset_connection
The connection is contextual. For example, there are uncommitted transactions in the current connection, available cursors, and temporary tables. Therefore, in order to facilitate repeated use of connections, the next connection will not be affected by the previous connection. SQL Server uses sys. sp_reset_connection to clear the context of the current connection so that the other connection can continue to be used.

When Connection. Close () is called in Ado.net, sys. sp_reset_connection is triggered. This system stored procedure will probablyDo the following::
Close cursor
Clear temporary objects, such as temporary tables
Release lock
Reset Set options
Reset statistics
Roll back uncommitted transactions
Switch to the connected default database
Reset Trace Flag
In addition, according to the information on the BOL:
Copy codeThe Code is as follows:
"The sp_reset_connection stored procedure is used by SQL
Server to support remote stored procedure callin a transaction. This stored
Procedure also causes Audit Login and Audit Logout events to fire when
Connection is reused from a connection pool ."

You can know that sys. sp_reset_connection cannot be explicitly called in SQL Server. In addition, this method will trigger the Audit Login and Audit Logout events.

A simple example
The following is a simple example to show how to use the connection pool:
First, I use four connections. The first and second connections have a waiting time of 10 seconds:
Copy codeThe Code is as follows:
String ConnectionString = "data source =. \ sql2012; database = AdventureWorks; uid = sa; pwd = sasasa ";
SqlConnection cn1 = new SqlConnection (ConnectionString );
SqlCommand cmd1 = cn1.CreateCommand ();
Optional 1.commandtext = "SELECT * FROM dbo. ABCD ";
Cn1.Open ();
Secrets 1.executereader ();
Cn1.Close ();
Response. Write ("connection close time:" + DateTime. Now. ToLongTimeString () + "<br/> ");
System. Threading. Thread. Sleep (10000 );
SqlConnection cn2 = new SqlConnection (ConnectionString );
SqlCommand cmd2 = cn2.CreateCommand ();
Optional 2.commandtext = "SELECT * FROM dbo. ABCD ";
Cn2.Open ();
Other 2.executereader ();
Cn2.Close ();
Response. Write ("connection close time:" + DateTime. Now. ToLongTimeString () + "<br/> ");
SqlConnection cn3 = new SqlConnection (ConnectionString );
SqlCommand cmd3 = cn3.CreateCommand ();
Optional 3.commandtext = "SELECT * FROM dbo. ABCD ";
Cn3.Open ();
Worker 3.executereader ();
Cn3.Close ();
Response. Write ("connection close time:" + DateTime. Now. ToLongTimeString () + "<br/> ");
System. Threading. Thread. Sleep (1500 );
SqlConnection cn4 = new SqlConnection (ConnectionString );
SqlCommand syntax 4 = cn4.CreateCommand ();
Ipv4.commandtext = "SELECT * FROM dbo. ABCD ";
Cn4.Open ();
Ipv4.executereader ();
Cn4.Close ();
Response. Write ("connection close time:" + DateTime. Now. ToLongTimeString () + "<br/> ");

Below we use Profile:

First, we can see that every Close () method triggers exec sp_reset_connection
In addition, we can see that the SP51 is constantly waiting for 10 seconds in the middle. The remaining connections all use the SPID51 connection. Although Ado.net has closed several times, however, physical connections are not interrupted.
Therefore, the connection pool greatly improves the efficiency.