This article originated in the GCR MVP Open Day and the C # MVP to discuss the concept of connection pooling. So write a separate article to dissect the connection pool.
Why do I need a connection pool
To dissect a technology the first question is why the technology exists.
For each connection to SQL Server, you will need to go through the TCP/IP protocol three times handshake, identity authentication, establish a connection in SQL Server, allocate resources, etc. When the client closes the connection, the client terminates the physical connection with SQL Server. However, as anyone who has done database development knows, it is normal to shut down a connection after each operation, and a single application may have multiple connections at the same time, even if it is not heavily loaded and needs to be connected to SQL Server and close the connection.
As a result, it would be a waste of resources if you constantly build and close connections like this. Therefore, there is a mechanism for connection pooling in ado.net. Maintain connection pooling in the application process for clients in SQL Server. Unified management of Ado.net and SQL Server connections, both to connect pooling and SQL Server connections, and when Connection.Open (), only a connection that has been established with SQL Server is allocated from the connection pool. When Connection.close () is not physically disconnected from SQL Server, it simply reclaims the connection.
Therefore, connection pooling always maintains a certain number of connections to SQL Server so that applications can reuse these connections to reduce performance losses.
resetting the sys.sp_reset_connection of a connection
The connection is contextual, such as the current connection has uncommitted transactions, there are available cursors, and there is a corresponding temporary table. Therefore, in order for the connection to be reused so that the next connection does not receive the impact of the previous connection, SQL Server clears the context of the current connection through sys.sp_reset_connection so that another connection can continue to be used.
When Connection.close () is invoked in Ado.net, the sys.sp_reset_connection is triggered. This system stored procedure will probably
do the following things:
Close cursor
Clears temporary objects, such as temporary tables
Release lock
Reset SET options
Resetting statistics
Rollback OF uncommitted transactions
Switch to the connection's default database
Reset Trace Flag
In addition, according to the information on the BOL:
Copy Code code as follows:
"The sp_reset_connection stored procedure is used by SQL
Server to support remote stored procedure calls in a transaction. This stored
Procedure also causes Audit Login and Audit Logout events to fire when a
Connection is reused from connection pool. "
You know that you cannot explicitly call sys.sp_reset_connection in SQL Server, and this method also triggers audit login and audit logout events.
a simple example
Here's a simple example to see the use of connection pooling:
First I use four connections, where the first and second connections have a 10-second wait time:
Copy Code code as follows:
String ConnectionString = "Data Source=.\\sql2012;database=adventureworks;uid=sa;pwd=sasasa";
SqlConnection cn1=new SqlConnection (ConnectionString);
SqlCommand cmd1=cn1. CreateCommand ();
cmd1.commandtext= "SELECT * FROM dbo. ABCD ";
Cn1. Open ();
Cmd1. ExecuteReader ();
Cn1. Close ();
Response.Write ("Connection Shutdown Time:" +datetime.now.tolongtimestring () + "<br/>");
System.Threading.Thread.Sleep (10000);
SqlConnection cn2=new SqlConnection (ConnectionString);
SqlCommand cmd2=cn2. CreateCommand ();
cmd2.commandtext= "SELECT * FROM dbo. ABCD ";
Cn2. Open ();
Cmd2. ExecuteReader ();
Cn2. Close ();
Response.Write ("Connection Shutdown Time:" +datetime.now.tolongtimestring () + "<br/>");
SqlConnection cn3=new SqlConnection (ConnectionString);
SqlCommand cmd3=cn3. CreateCommand ();
cmd3.commandtext= "SELECT * FROM dbo. ABCD ";
Cn3. Open ();
Cmd3. ExecuteReader ();
Cn3. Close ();
Response.Write ("Connection Shutdown Time:" +datetime.now.tolongtimestring () + "<br/>");
System.Threading.Thread.Sleep (1500);
SqlConnection cn4=new SqlConnection (ConnectionString);
SqlCommand Cmd4=cn4. CreateCommand ();
cmd4.commandtext= "SELECT * FROM dbo. ABCD ";
Cn4. Open ();
Cmd4. ExecuteReader ();
Cn4. Close ();
Response.Write ("Connection Shutdown Time:" +datetime.now.tolongtimestring () + "<br/>");
Here we can use the profile screenshot:
We can first see that every close () method triggers the EXEC sp_reset_connection
In addition, we wait in the middle of 10 seconds can also see SP51 is constant, the remaining few connections are all SPID51 this connection, although ado.net close several times, but actually physical connection is not interrupted.
Therefore, it can be seen that the connection pool greatly enhance the efficiency.
