SQL Server Performance

SQL Connection not closing

Discussion in 'General Developer Questions' started by Chuck L. King, Jul 8, 2003.

  1. Chuck L. King New Member

    We are opening and closing a sql connection in our code and when we traced it in SQL Profiler we noticed that the connection is not closing. It eventually closes once it times out. The code snippet is below:

    ------------------------------------------------------------
    private void Page_Load(object sender, System.EventArgs e)
    {
    String strConn="";
    SqlConneciton sqlConnRes=null;
    dgdResource.DataBind();
    try
    {
    strConn = System.Configuration.ConfigurationSettings.AppSettings.Get("PlusRS");
    sqlConnRes = new SqlConnection(strConn);
    sqlConnRes.Open();
    }
    catch (Exception ex)
    {
    Response.Write(ex.Mesage);
    }
    finally
    {
    //close connection
    try
    {
    sqlConnRes.Close();
    sqlConnRes.Dispose();
    sqlConnRes = null;
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message);
    }
    }
    }
    ----------------------------------------------
    Any ideas?

    Thanks, Chuck
    cking@cntllp.com

  2. Argyle New Member

    I see you use ASP.NET code. The connection is not closing because the webserver is using connection pooling. This means that the webserver will keep the connection open a while (60 or 90 seconds or something) just incase a new connection request comes along. This is a good thing because the webserver then doesn't have to do the handshake (check security conetext etc.) with SQL server all over again (can take 5-15 seconds sometimes) but can use an existing connection from the pool.

    If the number of requests decrease unused connections in the pool will automaticly be closed.

    Connections in the pool can be re-used if the connectionstring is identical between requests (which often is the case with a web site).

    /Argyle

Share This Page