Diagnosing SqlClient Connection Pool Fragmentation

This guide is part of .NET & ADO.NET Connection Pooling. SqlClient keys its pools on the exact connection string, and — when integrated security is used — on the Windows identity of the caller as well. Anything that varies between calls creates a separate pool with its own Max Pool Size, its own idle connections and its own minimum. The symptom is a database whose connection count is many times what the configuration seems to allow, alongside pool timeouts in an application that appears to be nowhere near its limit:

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining
a connection from the pool. This may have occurred because all pooled connections were in use
and max pool size was reached.
-- yet the server shows far more than Max Pool Size × instances
SELECT COUNT(*) FROM sys.dm_exec_connections;   -- 1,840

The objective is to find every distinct pool the process has created, understand which difference caused each one, and consolidate them into one pool per logical database and role.

Rapid incident diagnosis

Count the pools from inside the process. SqlClient publishes event counters; dotnet-counters reads them without a redeploy:

dotnet-counters monitor --process-id $(pgrep -f Orders.Api) \
  --counters Microsoft.Data.SqlClient.EventSource[active-hard-connections,number-of-pools,number-of-active-connections,number-of-free-connections,number-of-non-pooled-connections]

number-of-pools is the number this page is about. A service that connects to one database with one identity should report 1. Anything above that is fragmentation, and the count itself hints at the cause: a handful suggests a few code paths building strings differently; hundreds suggests a per-tenant or per-token string.

Find the differing strings. Log the canonical form of every connection string the process uses, with the password removed, and count the distinct values:

static string Fingerprint(string cs)
{
    var b = new SqlConnectionStringBuilder(cs) { Password = "" };
    return b.ConnectionString;   // normalised ordering and casing, password removed
}

// at construction time, in a debug build or behind a feature flag
logger.LogInformation("connection string fingerprint {Hash}",
    Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(Fingerprint(cs)))).AsSpan(0, 8).ToString());

Two fingerprints that differ by one setting — Application Name, Current Language, Workstation ID, a trailing semicolon, different casing of Initial Catalog — are two pools.

Check the server’s view by login and program. SQL Server attributes sessions to a login and a program name, which is usually enough to identify the fragmenting dimension:

SELECT login_name, program_name, host_name, COUNT(*) AS sessions
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name, program_name, host_name
ORDER BY sessions DESC;

Many logins from one host means identity-based fragmentation; many program names means the application varies Application Name per module or per tenant.

Look for token-based fragmentation. With Microsoft Entra authentication, an access token supplied through SqlConnection.AccessToken does not change the connection string, so it does not fragment. A token embedded in the connection string does. Check which pattern the code uses:

grep -rn "AccessToken\s*=\|Authentication=Active Directory\|access_token" --include=*.cs src/
Sources of SqlClient pool fragmentation Four variations — a per-tenant application name, a differing Initial Catalog case, a per-user integrated security identity and a token inside the connection string — each create separate pools, multiplying the effective connection limit. One process one database Pool key string + identity Application Name per tenant Initial Catalog case differs integrated security per user token inside the string each card is a separate pool with its own Max Pool Size
SqlClient keys pools on the exact connection string and, for integrated security, the caller's identity. Every variation is a separate pool with its own maximum.

Mathematical sizing or parameter formula

Fragmentation multiplies the configured maximum:

effective_max = pools × Max Pool Size × instances

A service with Max Pool Size = 100, twelve instances and — unknowingly — four pools per process can open 4 × 100 × 12 = 4,800 connections, against a configuration that appears to allow 1,200.

It also reduces effective capacity, which is the part that produces timeouts. Each pool serves only the calls whose key matches it, so a burst on one tenant exhausts that tenant’s pool while the others sit idle:

capacity_available_to_one_call_path = Max Pool Size        (not pools × Max Pool Size)
idle_waste ≈ (pools − 1) × Min Pool Size × instances

With Min Pool Size = 5, four pools and twelve instances, 180 connections are held permanently for pools that may be nearly unused.

Sizing after consolidation. One pool per database and role, sized from in-flight work:

Max Pool Size = ceil( requests_per_second × queries_per_request × mean_query_seconds ÷ 0.7 )
fleet         = instances × Max Pool Size ≤ usable_server_connections

At 300 requests per second, 2 queries of 10 ms: 300 × 2 × 0.01 = 6 in flight, so 9; twelve instances claim 108 connections rather than thousands.

Where several pools are legitimate, budget them explicitly: a writer pool and a reader pool are two pools by design, and each needs its own size derived from its own traffic.

Effect of fragmentation on total and usable capacity Four pools of one hundred connections across twelve instances allow 4,800 connections in total, while any single call path can still only use 100 of them. Max Pool Size 100, 12 instances total the server may see 4,800 usable by one call path 100 the server is oversubscribed and the application still times out consolidating to one pool fixes both numbers at once
Fragmentation both multiplies the fleet's connection total and shrinks the capacity available to any one call path, which is why it produces timeouts and an overloaded server at the same time.

Exact remediation & configuration

Build the connection string once. Construct it at startup, register it, and never assemble one per call:

// Program.cs
var csb = new SqlConnectionStringBuilder(builder.Configuration.GetConnectionString("Orders"))
{
    ApplicationName = "orders-api",          // constant, never per tenant or per request
    MaxPoolSize = 9,
    MinPoolSize = 2,
    ConnectTimeout = 3,
    ConnectRetryCount = 3,
    ConnectRetryInterval = 10,
    Pooling = true,
};
var connectionString = csb.ConnectionString;

builder.Services.AddSingleton(_ => new SqlConnectionFactory(connectionString));

Pass identity out of band, not in the string. With Entra authentication, set AccessToken on the connection object; the pool key stays constant while the token rotates:

public sealed class SqlConnectionFactory(string connectionString)
{
    private readonly TokenCredential _credential = new DefaultAzureCredential();

    public async Task<SqlConnection> OpenAsync(CancellationToken ct)
    {
        var conn = new SqlConnection(connectionString);          // one pool key
        var token = await _credential.GetTokenAsync(
            new TokenRequestContext(["https://database.windows.net/.default"]), ct);
        conn.AccessToken = token.Token;                          // does not affect pooling
        await conn.OpenAsync(ct);
        return conn;
    }
}

Handle multi-tenancy without a pool per tenant. Where tenants share a database, switch context inside the transaction rather than in the connection string:

await using var conn = await factory.OpenAsync(ct);
await using var tx = await conn.BeginTransactionAsync(ct);
await using (var cmd = new SqlCommand("EXEC sp_set_session_context @key = N'tenant', @value = @t", conn, (SqlTransaction)tx))
{
    cmd.Parameters.AddWithValue("@t", tenantId);
    await cmd.ExecuteNonQueryAsync(ct);
}
// queries here read SESSION_CONTEXT(N'tenant') in their predicates or row-level security policy
await tx.CommitAsync(ct);

Where tenants have separate databases, one pool per database is legitimate — but then Max Pool Size must be small, because the number of pools equals the number of tenants.

Avoid integrated-security fragmentation. Under impersonation, each Windows identity gets its own pool. Run the application under a single service account and authorise users in the application rather than at the database connection, unless per-user database auditing is a hard requirement.

Normalise anything that varies. A trailing semicolon, Data Source=SERVER versus Data Source=server, or Application Name set in one code path and not another all fragment. Constructing through SqlConnectionStringBuilder from a single configuration value removes the whole class of problem.

Consider ClearPool only for recovery. SqlConnection.ClearPool(conn) discards a pool whose connections are known bad — after a failover, for instance. It is a recovery tool, not a fix for fragmentation, and calling it routinely just adds churn.

Preventing regressions

Fragmentation returns quietly, usually through a well-intentioned change: adding the tenant name to Application Name so queries are easier to attribute, or building a string in a new module.

Assert the pool count in tests. The event counters are readable in-process, so an integration test can fail the build when a change creates a second pool:

[Fact]
public async Task Application_uses_exactly_one_pool()
{
    await ExerciseAllEndpoints();                      // hits every data access path
    var pools = SqlClientEventCounters.NumberOfPools;  // read via EventListener in the test host
    Assert.Equal(1, pools);
}

An EventListener subscribed to Microsoft.Data.SqlClient.EventSource collects the counter without external tooling.

Alert on the server’s session attribution. A new program_name or a growing set of logins from the application’s hosts is the production signal:

SELECT COUNT(DISTINCT program_name) AS distinct_programs,
       COUNT(DISTINCT login_name)   AS distinct_logins
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND host_name LIKE 'orders-%';

Anything above the expected count deserves investigation before it becomes a connection incident.

Review connection string construction in code review. One rule catches most regressions: connection strings are built in exactly one place, from configuration, at startup. Anything else — string interpolation, per-request builders, appending a tenant — is a finding.

Watch the counters continuously. Publishing number-of-pools and number-of-active-connections as metrics costs almost nothing and makes the next occurrence obvious:

Counter Healthy Meaning when it rises
number-of-pools 1 per database and role A new pool key appeared
number-of-active-connections below Max Pool Size Normal utilisation
number-of-free-connections small, stable Idle connections held
number-of-non-pooled-connections 0 Something set Pooling=false
active-hard-connections stable Physical connections being opened
Consolidating pool keys Four fragmented pools each serve a subset of calls with their own maximum. After consolidation, one pool serves every call path with a single sized maximum. tenant A pool tenant B pool tenant C pool One pool sized from in-flight work every call path shares it consolidate constant string token out of band tenant in session context legitimate exceptions: a writer pool and a reader pool, each sized separately
One pool per database and role is the target. Every additional pool key both multiplies the fleet's connections and shrinks the capacity any single request can reach.

Validation & verification

One pool, verified in production. After the change, the counter must report the expected number under real traffic, not just in tests:

dotnet-counters monitor --process-id $(pgrep -f Orders.Api) \
  --counters Microsoft.Data.SqlClient.EventSource[number-of-pools]

Server connections match the arithmetic. Total sessions from the service should equal about instances × Max Pool Size:

SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND program_name = 'orders-api';

Timeouts disappear at the same load. The load that previously produced pool timeouts should now complete with connections to spare, because the whole Max Pool Size is available to every call path rather than a quarter of it.

Token rotation does not create pools. Run for longer than the token lifetime — an hour is typical — and confirm number-of-pools is unchanged, which proves the token is being supplied out of band.

Frequently Asked Questions

Does Application Name really create a separate pool?
Yes. It is part of the connection string, and SqlClient’s pool key is the string as written. It is genuinely useful for attribution, so set it once per service rather than per tenant or per module.
Is fragmentation a problem in Npgsql too?
The same rule applies: a pool per distinct connection string. Npgsql’s NpgsqlDataSource makes the correct pattern easier to follow, and the rotating-token case is covered by its periodic password provider, as described in Diagnosing Npgsql Connection Pool Exhaustion.
What about Enlist and distributed transactions?
Connections enlisted in a transaction are not returned to the general pool until the transaction completes, and SqlClient keeps transaction-affiliated connections separately. Long-lived distributed transactions therefore reduce effective pool capacity in a way that looks similar to fragmentation.
Does connection pooling work at all with Integrated Security and impersonation?
It does, but with a pool per impersonated identity, which is rarely what anyone intends. A single service account with application-level authorisation is almost always the better design.