ExecuteNonQuery leaves a sleeping awaiting command session

Question

I have a command-line utility written in C#, which updates some records in the database and then enters a loop checking some other stuff. For some reason, the SQL execution always leaves a sleeping awaiting command session in SQL server 2008 after SQL execution. I can use SQL server activity monitor and run sp_who2 to confirm that. But SQL transaction has been committed successfully indeed. I can tell this from debugging in the code and the timestamp of the data record in the database. Also, the open sleeping session will be killed when I explicitly stop the command-line utility. This indicates that my function creates some SQL connection object that can’t be disposed of until it is garbage collected by CLR when the application is aborted. How could this happen? Thanks. Here is the code:

bool result = false;
using (SqlConnection conn = new SqlConnection(this.Connection))
{

    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText =
                @"if not exists (select id from pl where sj=@sj and ej=@ej and dateInactivated is null) 
                    insert into pl(sj,ej,pf, br, tk, lastModified )
                    values(@sj,@ej,@pf,@br,@tk,getDate())
                else
                    update pl set sj=@sj,ej=@ej,pf=@pf,br=@br,tk=@tk,lastModified=getDate()
                    where sj=@sj and ej=@ej and dateInactivated is null
                ";

        cmd.Parameters.AddWithValue("@sj", sj);
        cmd.Parameters.AddWithValue("@ej", ej);
        cmd.Parameters.AddWithValue("@pf", pf);
        cmd.Parameters.AddWithValue("@br", br);
        cmd.Parameters.AddWithValue("@tk", tkData);
        cmd.CommandTimeout = 60;
        SqlTransaction trans = null;
        try
        {
            conn.Open();
            trans = conn.BeginTransaction();
            cmd.Transaction = trans;
            cmd.ExecuteNonQuery();
            trans.Commit();
            conn.Close();
            result = true;
        }
        catch (SqlException ex)
        {
            if (trans != null)
            {
                trans.Rollback();
            }
            Log.WriteLog(LogLevel.ERROR, ex.Message);
            result = false;
        }
        finally
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }
    }

}

return result;

Note that tk field is an xml database field.

score 8 · Accepted Answer · answered Aug 28 '12 at 17:57

8

This is called connection pooling, and you want it to work this way.

Basically, dropping a connection entirely and then re-establishing it for your app incurs a much higher penalty than just re-using an idle connection that was left behind from a previous invocation of your code. This is a good thing.

answered Aug 28 '12 at 17:57

Aaron Bertrand

272,866
37
466
490

I thought about of connection pooling as well. But my understanding was that it is really just some cache objects in managed heap. Thanks for the info. That is good to know. – Tuzki Aug 28 '12 at 18:12
It is just a some cache objects in the managed heap... that hold the unmanaged handle to the connection. – Jon Hanna Aug 28 '12 at 22:12

score 4 · Answer 2 · answered Aug 28 '12 at 18:07

It's due to pooling. 99.99% of the time, this is a great thing - it makes for fewer connections being made and torn down, and if multi-threaded makes for fewer connections being open simultaneously.

If you add Pooling=false to the connection string, it won't be used. About the only reasons for doing this are:

As a temporary measure if you're leaking pooled connections somewhere - note that this makes a bad situation worse as far as performance goes, but it won't hit the max pool size. Heavy emphasis on the temporary here.
You have client applications sharing a database, and using connections for very small lengths of time. Here you want to turn off pooling because while it makes each client less efficient, it reduces strain on the server itself.

The second is very rare. The first should be very rare - again, emphasis on the temporary.

ExecuteNonQuery leaves a sleeping awaiting command session

2 Answers2