ADO.NET and Connection Pooling

I decided that I needed to learn more about ConnectionPooling – especially ConnectionPool fragmentation.   I ran into a great article here that explains in the ins and outs of connection pool fragmentation.  I decided to try out some scenarios.

I first created a class library that calls a select on a Northwind table:

public class NorthwindFactory
{
    public Dictionary<String, String> GetRegions(String connectionString)
    {
        Dictionary<String, String> regionDictionary = new Dictionary<string, string>();

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            String commandText = "Select * from Region";
            using(SqlCommand command = new SqlCommand(commandText, connection))
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    regionDictionary.Add(reader[0].ToString(), reader[1].ToString());
                }
            }
        }

        return regionDictionary;
    }
}

I then added a unit (really integration) test to run this function:

[TestClass()]
public class NorthwindFactoryTests
{

    [TestMethod()]
    public void GetRegionsTest()
    {
        NorthwindFactory target = new NorthwindFactory();
        string connectionString = @"Data Source=Dixon12;database=Northwind;Uid=NorthwindUser;Pwd=password";
        Dictionary<string, string> regionDictionary = target.GetRegions(connectionString);

        Int32 expected = 4;
        Int32 actual = regionDictionary.Count;
        Assert.AreEqual(expected, actual);
    }
}

I then opened up Sql Server Management Studio to see the impact that this call had:

select spid, loginame, status, program_name,cmd from master..sysprocesses where spid > 50

When I ran the test, nothing came out of sysprocess – by the time I flipped windows from VS to SSMS, the test ran and the connection went away.  I changed the code to allow me to flip over:

while (reader.Read())
{
    Thread.Sleep(TimeSpan.FromSeconds(3));
    regionDictionary.Add(reader[0].ToString(), reader[1].ToString());
}

Sure enough, when I run the test, I see the active connection

I then decided to see what would happen with two connections.  I went and added a second connection in serial:

[TestMethod()]
public void GetTwoRegionsInSequenceTest()
{
    NorthwindFactory target = new NorthwindFactory();
    string connectionString = @"Data Source=Dixon12;database=Northwind;Uid=NorthwindUser;Pwd=password";
    Dictionary<string, string> regionDictionaryOne = target.GetRegions(connectionString);
    Dictionary<string, string> regionDictionaryTwo = target.GetRegions(connectionString);

    Int32 expected = 8;
    Int32 actual = regionDictionaryOne.Count + regionDictionaryTwo.Count;
    Assert.AreEqual(expected, actual);
}

There is only 1 active connection at a time – the connectionPoolManager in action.

My next though was that if the connection is explicitly closed by the SqlConnection objects is not out of its using scope, would the connection stay open?

using(SqlCommand command = new SqlCommand(commandText, connection))
{
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        regionDictionary.Add(reader[0].ToString(), reader[1].ToString());
    }
    connection.Close();
    Thread.Sleep(TimeSpan.FromSeconds(10));
}

Sure enough, when I run this, the connection is “closed” on the client but on the Sql Server it is still active:

That is the connection pool manager keeping the connection alive.

So my next thought – does the connection pool manager work cross threads?  I created a new test like so:

[TestMethod()]
public void GetRegions_ParallelTest()
{
    NorthwindFactory target = new NorthwindFactory();
    string connectionString = @"Data Source=Dixon12;database=Northwind;Uid=NorthwindUser;Pwd=password";
    ConcurrentBag<KeyValuePair<String, String>> regionBag = new ConcurrentBag<KeyValuePair<String, String>>();

    Parallel.For(0, 2, i =>
    {
        Dictionary<string, string> regionDictionary = target.GetRegions(connectionString);
        foreach (KeyValuePair<String,String> keyValuePair in regionDictionary)
        {
            regionBag.Add(keyValuePair);
        }

    });

    Int32 expected = 8;
    Int32 actual = regionBag.Count;
    Assert.AreEqual(expected, actual);
}

And the GetRegions() has a 10 second delay built in.  I got this back on my dual-processor machine:

And to corroborate, I passed in two different times that the thread stays awake:

public Dictionary<String, String> GetRegions(String connectionString)
{
    return GetRegions(connectionString, 10);
}

public Dictionary<String, String> GetRegions(String connectionString, Int32 lengthOfSleep)
{
    Dictionary<String, String> regionDictionary = new Dictionary<string, string>();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        String commandText = "Select * from Region";
        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                regionDictionary.Add(reader[0].ToString(), reader[1].ToString());
            }
            connection.Close();
            Thread.Sleep(TimeSpan.FromSeconds(lengthOfSleep));
        }
    }

    return regionDictionary;
}

Add the test that checks:

[TestMethod()]
public void GetRegions_Parallel_DifferentSleepTimes_Test()
{
    NorthwindFactory target = new NorthwindFactory();
    string connectionString = @"Data Source=Dixon12;database=Northwind;Uid=NorthwindUser;Pwd=password";
    ConcurrentBag<KeyValuePair<String, String>> regionBag = new ConcurrentBag<KeyValuePair<String, String>>();

    Parallel.For(0, 2, i =>
    {
        Dictionary<string, string> regionDictionary = target.GetRegions(connectionString, 5+(i*5));
        foreach (KeyValuePair<String, String> keyValuePair in regionDictionary)
        {
            regionBag.Add(keyValuePair);
        }

    });

    Int32 expected = 8;
    Int32 actual = regionBag.Count;
    Assert.AreEqual(expected, actual);
}

Sure enough, in the 1st five seconds:

And in the last 5 seconds (or so):

Next, I added a new test that uses two different types of connection strings – 1 for integrated security, 1 for Sql Server security:

[TestMethod()]
public void GetRegions_DifferentConnectionStringsTest()
{
    NorthwindFactory target = new NorthwindFactory();
    string sqlServerSecurityConnectionString = @"Data Source=Dixon12;database=Northwind;Uid=NorthwindUser;Pwd=password";
    string integratedSecurityConnectionString = @"Data Source=Dixon12;database=Northwind;Integrated Security=true";

    Dictionary<string, string> sqlServerSecurityRegionDictionary = target.GetRegions(sqlServerSecurityConnectionString);
    Dictionary<string, string> integratedSecurityRegionDictionary = target.GetRegions(integratedSecurityConnectionString);

    Int32 expected = 8;
    Int32 actual = sqlServerSecurityRegionDictionary.Count + integratedSecurityRegionDictionary.Count;
    Assert.AreEqual(expected, actual);
}

Sure enough, 2 connections

 

I then realized that my test so far do not show the number of connection pools in existence, rather it just shows the number of active Sql Connections for each scenario that may or may not be controlled by different connection pools.  What I need to do is to have something on the client that I can use to inspect the number of connection pools and connections within those pools.  I ran across this site which showed how to use reflection to resolve the hidden properties/fields of the SqlConnection class.  To that end, I created the following class that determines the the ConnectionPool for a given connection and then the number of connections in that pool.

public static ConnectionPool GetConnectionPool(SqlConnection sqlConnection)
{
    ConnectionPool connectionPool = new ConnectionPool();
    connectionPool.PoolIdentifier = sqlConnection.ConnectionString;

    Type sqlConnectionType = typeof(SqlConnection);
    FieldInfo _poolGroupFieldInfo =
      sqlConnectionType.GetField("_poolGroup", BindingFlags.NonPublic | BindingFlags.Instance);
    var dbConnectionPoolGroup =
      _poolGroupFieldInfo.GetValue(sqlConnection);

    if (dbConnectionPoolGroup != null)
    {
        
        FieldInfo _poolCollectionFieldInfo =
          dbConnectionPoolGroup.GetType().GetField("_poolCollection",
             BindingFlags.NonPublic | BindingFlags.Instance);
        
        HybridDictionary poolCollection =
          _poolCollectionFieldInfo.GetValue(dbConnectionPoolGroup) as HybridDictionary;

        foreach (DictionaryEntry poolEntry in poolCollection)
        {
            var foundPool = poolEntry.Value;
            FieldInfo _objectListFieldInfo =
               foundPool.GetType().GetField("_objectList",
                  BindingFlags.NonPublic | BindingFlags.Instance);
            var listTDbConnectionInternal =
               _objectListFieldInfo.GetValue(foundPool);
            MethodInfo get_CountMethodInfo =
                listTDbConnectionInternal.GetType().GetMethod("get_Count");
            var numberOfConnections = get_CountMethodInfo.Invoke(listTDbConnectionInternal, null);
            connectionPool.NumberOfConnections = (Int32)numberOfConnections;
        }
    }

    return connectionPool;
}

I also realized that I needed the number of ConnectionPools in total.  That is also available via the SqlConnection.ConnectionFactory property. 

public static List<ConnectionPool> GetConnectionPools(SqlConnection sqlConnection)
{
    List<ConnectionPool> connectionPools = new List<ConnectionPool>();

    Type sqlConnectionType = typeof(SqlConnection);
    PropertyInfo _connectionFactoryPropertyInfo =
        sqlConnectionType.GetProperty("ConnectionFactory", BindingFlags.NonPublic | BindingFlags.Instance);
    var connectionFactory =
      _connectionFactoryPropertyInfo.GetValue(sqlConnection,null);


    if (connectionFactory != null)
    {
        FieldInfo _connectionPoolGroupsInfo =
          connectionFactory.GetType().BaseType.GetField("_connectionPoolGroups",
             BindingFlags.NonPublic | BindingFlags.Instance);
        var dbConnectionPoolGroups =
          _connectionPoolGroupsInfo.GetValue(connectionFactory);

        IEnumerable enumerator = dbConnectionPoolGroups as IEnumerable;
        ConnectionPool connectionPool = null;

        foreach (var item in enumerator)
        {
            connectionPool = new ConnectionPool();
            PropertyInfo _valuePropertyInfo =
                item.GetType().GetProperty("Value", BindingFlags.Public | BindingFlags.Instance);
            var _valuePropertyValue = _valuePropertyInfo.GetValue(item,null);

            PropertyInfo _keyPropertyInfo =
                item.GetType().GetProperty("Key", BindingFlags.Public | BindingFlags.Instance);
            var _keyPropertyValue = _keyPropertyInfo.GetValue(item, null);

            if (_valuePropertyValue != null)
            {
                FieldInfo _poolCollectionFieldInfo =
                    _valuePropertyValue.GetType().GetField("_poolCollection",
                     BindingFlags.NonPublic | BindingFlags.Instance);
                HybridDictionary poolCollection =
                  _poolCollectionFieldInfo.GetValue(_valuePropertyValue) as HybridDictionary;

                connectionPool.PoolIdentifier = _keyPropertyValue.ToString();
                connectionPool.NumberOfConnections = poolCollection.Count;
            }
            connectionPools.Add(connectionPool);
        }
    }

    return connectionPools;
}

So my unit(integration) tests show that with the same connection string, you have 1 pool with as many connection.Open() you call that have not been cleaned up but the GC yet. 

[TestMethod()]
public void GetConnectionPool_1OpenConnectionTest()
{
    string connectionString = @"Data Source=Dixon12;database=Northwind;Uid=NorthwindUser;Pwd=password";
    SqlConnection sqlConnection = new SqlConnection(connectionString);
    sqlConnection.Open();
    ConnectionPool connectionPool = ConnectionPoolFactory.GetConnectionPool(sqlConnection);

    Int32 expected = 1;
    Int32 actual = connectionPool.NumberOfConnections;
    Assert.AreEqual(expected, actual);
    sqlConnection.Close();
}

Also, you can see the number of ConnectionPools that are active at any 1 time and the number of connections in those strings. 

[TestMethod()]
public void GetConnectionPools_2OpenConnectionsDifferentConnectionStringsTest()
{
    string connectionString1 = @"Data Source=Dixon12;database=Northwind;Uid=NorthwindUser;Pwd=password";
    SqlConnection sqlConnection1 = new SqlConnection(connectionString1);
    sqlConnection1.Open();
    string connectionString2 = @"Data Source=Dixon12;database=Northwind2;Integrated Security=true";
    SqlConnection sqlConnection2 = new SqlConnection(connectionString2);
    sqlConnection2.Open();
    List<ConnectionPool> connectionPools = ConnectionPoolFactory.GetConnectionPools(sqlConnection1);

    Int32 expected = 2;
    Int32 actual = connectionPools.Count;
    Assert.AreEqual(expected, actual);
}

Armed with that information, I could then confirm if different connection strings open new pools (it does) and that if different threads with the same connection string opens a new pool (it doesn’t). 

[TestMethod()]
public void GetConnectionPools_2OpenConnectionsSameConnectionStringsDifferentThreads_Test()
{
    string connectionString1 = @"Data Source=Dixon12;database=Northwind2;Integrated Security=true";
    SqlConnection sqlConnection1 = new SqlConnection(connectionString1);
    Thread threadOne = new Thread(sqlConnection1.Open);
    threadOne.Start();

    string connectionString2 = @"Data Source=Dixon12;database=Northwind2;Integrated Security=true";
    SqlConnection sqlConnection2 = new SqlConnection(connectionString2);
    Thread threadTwo = new Thread(sqlConnection2.Open);
    threadTwo.Start();

    List<ConnectionPool> connectionPools = ConnectionPoolFactory.GetConnectionPools(sqlConnection2);
    Int32 numberOfConnectionPoolsExpected = 1;
    Int32 numberOfConnectionPoolsActual = connectionPools.Count;
    Assert.AreEqual(numberOfConnectionPoolsExpected, numberOfConnectionPoolsActual);

    Int32 numberOfConnectionsExpected = 0;
    Int32 numberOfConnectionsActual = connectionPools[0].NumberOfConnections;
    Assert.AreEqual(numberOfConnectionsExpected, numberOfConnectionsActual);

}

This means that the connection pool manager is thread safe.  Note that Connections are not thread safe, which is why the # of connections are 0 on the main thread.  And yes, I know I could have looked at MSFT source code to figure this out and perhaps there is some documentation on Thread-Safety is available, but this was fun.

So the next question in my mind is what can have an impact on performance?  For example, if you have connection pool fragmentation (via different connection strings), what is the performance gain my combining all of the active connections into 1 pool?  This post has gotten long enough, so I will show that in another one.