Explanation
All the details above are “laboratory” experiments. What
is the science behind this? Can we configure this?
Let us review the error message we received when we are
doing the first review.
“Timeout expired.
The timeout period elapsed prior to obtaining a connection from the pool.
This may have occurred because all pooled connections were in use and max
pool size was reached.”
Before analyzing this error message, let us see technology
behind when a client makes a connection to SQL Server – http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx
has full details on this but I will summaries it using our laboratory
details.
In practice, most applications use only one or a few
different configurations for connections. This means that during application
execution, many identical connections will be repeatedly opened and closed. To
minimize the cost of opening connections, ADO.NET uses an optimization
technique called connection pooling.
Connection pooling reduces the number of times that new
connections must be opened. The pooler maintains ownership of the physical
connection. It manages connections by keeping alive a set of active connections
for each given connection configuration. Whenever a user calls Open on a
connection, the pooler looks for an available connection in the pool. If a
pooled connection is available, it returns it to the caller instead of opening
a new connection. When the application calls Close on the connection, the
pooler returns it to the pooled set of active connections instead of closing
it. Once the connection is returned to the pool, it is ready to be reused on
the next Open call. By default, maximum pool connection is 100 that is why
application are failing after reaching the connection to 100.
When a new connection is opened, if the
connection string is not an exact match to an existing pool, a new pool is
created. Connections are pooled per process, per application domain, per
connection string and when integrated security is used, per Windows identity.
That is why, when we execute the scenario 4, it uses two connection pools.
ADO.NET 2.0 introduced two new methods to clear the pool: ClearAllPools
and ClearPool. ClearAllPools clears the connection pools for a given
provider, and ClearPool clears the connection pool that is associated with a
specific connection. If there are connections being used at the time of the
call, they are marked appropriately. When they are closed, they are discarded
instead of being returned to the pool.
Let me put this into our sample code.
private void Connect_Click(object
sender, EventArgs e)
{
int i = 0;
while (1 == 1) //
Loop indefinitely
{
//Connection is create and connectiong to database
try
{
string conn = “Data
Source=.;Initial
Catalog=UserConnection;Integrated
Security=SSPI;enlist=false”;
SqlConnection sqlConn = new SqlConnection(conn);
sqlConn.Open();
//Store procedure insert_connection_details is
executed,
//looping instance value (i) will be passed
SqlCommand com = new
SqlCommand
(“insert_connection_details”,
sqlConn);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter
(“@connectionId”, SqlDbType.Int)).Value = i;
com.ExecuteNonQuery();
//if the Close check box is selected onlyConnection
will be closed
if (chkClose.Checked)
{
sqlConn.Close();
}
//if the dealy is not 0, there will be delayed of millisecond of
given value in Delay box
if (txtDelay.Text != “0”)
{
Thread.Sleep(Convert.ToInt32(txtDelay.Text));
}
}
catch (Exception
ex)
{
SqlConnection.ClearAllPools();// Clearing the connection pool
}
finally
{
i = i + 1;
}
}
Now if we execute the previous code again this is what you
will see in our perfmon.
Now application is running happily without failing. However,
you can observe that it is not clearing out the existing connection and the limit
of 100 is not applicable. At every horizontal line you are executing your pool
connection.
Conclusion
It is obvious to say you need to close your opened
connections. However as DBAs you can verify whether the connections that were
opened are closing by the application.
]]>