Leaving .net SqlConnection open indefinitely vs a using block

Question

I have a deep dive question. I want to understand why a SqlConnection is getting closed and how to detect whether connection pooling is actually taking place.

I have two different versions of a class which abstract talking to a database and returning a DataTable. What I am providing is an example of the problem/question.

Version 1 opens a SqlConnection upon instantiation and leaves it open. This is a single threaded process which handles incoming messages and writes the results to the database as fast as possible. That is the idea behind leaving SqlConnection perpetually open:

public class SpecialDbClientVersion1 : IDisposable
{
    string connStr;
    SqlConnection dbConn;

    public SpecialDbClientVersion1(string connStr)
    {
        this.connStr = connStr;
        this.dbConn = new SqlConnection(connStr);

        try
        {
            dbConn.Open();
        }
        catch (SqlException ex)
        {
            throw new Exception($"An exception occurred while trying to connect to {dbConn.Database}", ex);
        }
    }

    public DataTable QuerySqlReturnResultAsDataTable(string sql, int commandTimeout = 30)
    {
        try
        {
            var cmd = new SqlCommand(sql, dbConn);
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = commandTimeout;
            var dt = new DataTable();
            var adapter = new SqlDataAdapter(cmd);
            adapter.Fill(dt);
            return dt;
        }
        catch (Exception e)
        {
            throw new InvalidOperationException(e.Message);
        }
    }

    public void Dispose()
    {
        dbConn.Dispose();
    }
}

When I test this, everything goes fine and there is incredibly high throughput. Eventually though the SqlConnection gets closed for some reason. An error gets thrown and the party is over.

Question 1: can someone explain what would cause this connection to go into a Closed state without me calling the Close() method?

Now version 2 performs all the work in a using block. Per the internet, the runtime should magically use connection pooling under the hood. This is the best practice for working with SqlConnection objects per Microsoft. With connection pooling there should not be much of a performance hit.

public class SpecialDbClientVersion2
{
    string connStr;
    SqlConnection dbConn;

    public SpecialDbClientVersion2(string connStr)
    {
        this.connStr = connStr;
    }

    public DataTable QuerySqlReturnResultAsDataTable(string sql, int commandTimeout = 30)
    {
        using(var dbConn = new SqlConnection(connStr))
        {
            try
            {
                var cmd = new SqlCommand(sql, dbConn);
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = commandTimeout;
                var dt = new DataTable();
                var adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                throw new InvalidOperationException(e.Message);
            }
        }
    }
}

Question 2: how can I tell if connection pooling is actually being used? This writing the database needs to be as fast as possible and I am concerned that version 2 will have to re-establish a connection to the database every time. This would destroy performance. How can I measure whether connection pooling is occurring and whether it is impacting the throughput of the service?

I have been looking for Deep Dive resources on this subject but most of what I find is just "Use a SqlConnection in a using block and everything will be fine." I am looking for a deeper explanation than that. I really want to understand the guts of what is occurring here so that I hunt the correct performance issue. Thank you for whatever insight you can offer.

Answer 1

As others have mentioned, the connection may be being closed on the server side or by a idle timeout, check the server side logs for details.

For your 2nd part of your question, there are performance counters that tracks connection pool information. This can be viewed via code (see linked page for an example) setting the process id of your running program and displaying the value of things like NumberOfPooledConnections and NumberOfNonPooledConnections to see if the connection pool is actually being used by the process.

By turning on the off by default counters you could also monitor NumberOfFreeConnections which would tell you if there is a free open connection for your program to pick up and use to get that immediate response without the overhead of opening a connection you are asking for.