How to Reduce Connection Pools Hitting Multiple Databases

Microsoft’s connection pooling built in to the .Net Framework greatly improves performance by allowing hundreds of database calls to share the same pool of connections used to connect to the database. The .Net Framework creates a new pool of connections for each unique connection string. This works fantastic when your web application connects to a single database and all your connections use the same connection string. It even works well if you have two or three different connection strings, but for each unique connection string value, the .Net Framework creates a new connection pool. If your web site has dozens of different databases to connect to, you may find your application creates dozens of connection pools which begins to consume a lot of resources on both the web server and SQL Server.

There is a fairly easy way to eliminate this problem, and that is to use the ChangeDatabase method on the Connection object.

Our web application was connecting to one of fifty different databases that all reside on the same instance of SQL Server. Each of our clients has their own database. But our application was creating fifty different connection pools. We were able to have all the clients share a single pool of connections by first connecting to a neutral database, then redirecting the connection to the desired database.

We created a new database on the server, called Central, then we had all the connections to the database first connect to Central as the Initial Catalog. The next step was to call ChangeDatabase to switch to the desired database. This technique did not create a new connection to SQL Server and did not create a new connection pool on the .Net client. Microsoft documentation mentions this technique here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases , but their example code does not show the ChangeDatabase method, which is more efficient than their example.

In a nutshell, you can do this:

connection.Open();
connection.ChangeDatabase(“Client1”);

I created a simple Windows Form App to test the idea.  The test app runs a query to return the name of the current database it is in.  Also, on the SQL Server I run a query to see all the connections that exist.  Doing this I was able to prove that using ChangeDatabase did not create a new connection to SQL Server and my .Net SQL Connection Object was pointing at the correct desired database.  Sample code, query, and output is below:

Sample C# .Net Forms App:

using System;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace SQLConnectionTest
{
public partial class Form1 : Form
{
//https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
{
connection.Open();
RunSQL(connection, "select DB_NAME()");
}
using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=model;Trusted_Connection = yes"))
{
connection.Open();
RunSQL(connection, "select DB_NAME()");
}
using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=msdb;Trusted_Connection = yes"))
{
connection.Open();
RunSQL(connection, "select DB_NAME()");
}
using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
{
connection.Open();
RunSQL(connection, "select DB_NAME()");
}
using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
{ 
connection.Open();
connection.ChangeDatabase("TempDb");
RunSQL(connection, "select DB_NAME()");
}
using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
{
connection.Open();
RunSQL(connection, "select DB_NAME()");
}
using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
{
connection.Open();
connection.ChangeDatabase("TempDb");
RunSQL(connection, "select DB_NAME()");
connection.ChangeDatabase("model");
RunSQL(connection, "select DB_NAME()");
}
}
public static void RunSQL(SqlConnection connection, string queryString)
{
SqlCommand command = new SqlCommand(queryString, connection);
try
{
SqlDataReader reader = command.ExecuteReader(); 
if (reader != null)
{
while (reader.Read())
{
Console.WriteLine("\t{0}", reader[0]);
}
reader.Close();
}
}
catch (Exception ex){Console.WriteLine(ex.Message);}
}
public Form1()
{
InitializeComponent();
}
}
}

Output from Running the App written to console:

master
model
msdb
master
tempdb
master
tempdb
model

Query to see connections on SQL Server:

CREATE TABLE #TmpLog
(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),
CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)
INSERT INTO #TmpLog
EXEC sp_who2
SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName
FROM #TmpLog
where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’) and ProgramName like ‘.net%’
DROP TABLE #TmpLog
go

Results of running query on SQL Server: