Avoiding connection pool fragmentation

Microsofties admitted that some noticeable performance problems are possible while working with MS SQL Server via ADO.Net (link, scroll to Pool Fragmentation). The connection pooling technique used in ADO.Net to optimize and minimize the cost of opening connections reported to be "not so optimal" in particular scenarios.

The root of the problem is that the exact match between connection string and corresponding connection pool is required. Even if 2 connection strings are build from the same key-value pairs but differ in their order, you get 2 connection pools.

As a result, we get connection pool fragmentation, which is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance. Surprise for large multi-tenant application developers and maintainers!

From their side, Microsoft is not going to do anything about it in foreseeable future, instead they suggest to use a workaround: connect to master database and open the desired database with a separate command:

// Assumes that command is a SqlCommand object and that
// connectionString connects to master.
command.Text = "USE DatabaseName";
using (SqlConnection connection = new SqlConnection(
  connectionString))
  {
    connection.Open();
    command.ExecuteNonQuery();
  }

How can you achieve the same not leaving the zone of comfortable DataObjects.Net API? Time to get familiar with new member of DomainConfiguration class: ConnectionInitializationSql. The main purpose of the member is to literally execute the provided text as DbCommand right after a connection is opened.

Say, we are using Northwind database:


<domain
name="Default"
provider="sqlserver"
connectionString="Server=myServerAddress;Database=master;..."
connectionInitializationSql="USE NORTHWIND"
...>
<domain>


The feature is available in DataObjects.Net 4.6.4. Download DataObjects.Net