Monitor User Connections in SQL Server

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.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |