Tuning Max Pool Size in SqlClient Connection Strings

This guide is part of .NET & ADO.NET Connection Pooling. Max Pool Size defaults to 100 in SqlClient, which is a value chosen for a single application talking to a dedicated server and is wrong for essentially every modern deployment. Multiplied across replicas — and across the pools a service did not intend to create — it produces connection counts that exhaust any shared database.

The signature failure is a pair of errors that look unrelated and share a cause:

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.

Microsoft.Data.SqlClient.SqlException (0x80131904): Resource ID : 1.
The request limit for the database is 200 and has been reached.

The first is the client’s pool refusing to grow; the second is the server refusing more connections. Which one you see depends on whether Max Pool Size is below or above the server’s remaining capacity, and both are fixed by the same derivation.

Rapid Incident Diagnosis

Establish the real pool count before changing any value — the number of pools is more often wrong than the size of each.

  1. Count the server-side sessions by program name. On SQL Server, SELECT program_name, count(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 GROUP BY program_name ORDER BY 2 DESC. On Azure SQL the same view applies.
  2. Compare against Max Pool Size × replicas. If sessions substantially exceed that product, more than one pool exists per process, and the connection string is being constructed dynamically.
  3. Read the exception text. max pool size was reached names the client-side limit. request limit for the database names the server’s. They call for opposite adjustments.
  4. Check whether the sessions are doing anything. SELECT status, count(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 GROUP BY status. Many sleeping sessions with a full pool means connections are held rather than working.
Client limit versus server limit The client-side Max Pool Size and the server-side connection limit produce different exceptions and require opposite adjustments, so identifying which one fired is the first diagnostic step. "max pool size was reached" the client refused to grow the pool the database never saw these requests → raise the ceiling, if the budget allows or reduce hold time, which is usually better "request limit … has been reached" the server refused the connection every service on this database is affected → lower the ceiling, do not raise it raising it here makes the shared problem worse The instinct in both cases is to raise Max Pool Size. It is correct in one and actively harmful in the other.
Two errors, opposite remedies. Reading the exception text before adjusting anything is what prevents the second case being made worse.

Deriving the Value

The derivation is the standard one, with a .NET-specific twist: the divisor must count pools, not processes, because a process can hold several.

pools_per_process   = distinct connection strings used
processes           = maxReplicas × surge × worker_processes
max_pool_size       = floor(service_allowance ÷ (processes × pools_per_process))

A service with a 240-connection allowance, 12 replicas at surge, one process each, and two connection strings — a writer and a read replica — has a divisor of 24 and a per-pool ceiling of 10. Splitting that unevenly by measured traffic, perhaps 14 on the writer and 6 on the reader, is more accurate than giving both 10.

The value should sit comfortably above the concurrency the process actually reaches. A ceiling of 10 on a process whose peak concurrent query count is 3 is fine; a ceiling of 10 on one that regularly reaches 12 will queue. If the derived ceiling is below observed concurrency, the constraint is the allowance and the answer is fewer processes or a proxy, not a larger number.

Input Where To Find It Note
Service allowance Platform capacity plan Not max_connections — your share of it
maxReplicas Orchestrator autoscaler Not the current replica count
Surge Deployment strategy Adds 20–25% typically
Worker processes Host configuration Usually 1 for ASP.NET Core
Distinct connection strings Grep for GetConnectionString The term most often missed

Exact Remediation and Configuration

The change itself is one connection-string parameter, but two supporting changes make it safe.

// appsettings.Production.json
{
  "ConnectionStrings": {
    // 240 allowance ÷ (12 replicas at surge × 2 strings) = 10; writer weighted to 14.
    // RAISING maxReplicas REQUIRES RECOMPUTING THIS.
    "OrdersWriter": "Server=tcp:db.internal,1433;Database=orders;Max Pool Size=14;Min Pool Size=2;Connect Timeout=5;Application Name=orders-api-writer;",
    "OrdersReader": "Server=tcp:replica.internal,1433;Database=orders;Max Pool Size=6;Min Pool Size=1;Connect Timeout=5;Application Name=orders-api-reader;ApplicationIntent=ReadOnly;"
  }
}

Connect Timeout=5 matters as much as the ceiling. Its default of 15 seconds means a saturated pool holds a request thread for fifteen seconds before failing, which converts pool exhaustion into thread exhaustion. Five seconds, below the request timeout, fails fast enough to shed load.

Application Name must be constant. It becomes part of the pool key, so a per-tenant value creates a pool per tenant. Distinct values per service and per role are correct and valuable; distinct values per request are the failure described above.

Register the string once. The most reliable way to guarantee a single pool is to read the connection string into a variable at start-up and pass that variable everywhere, rather than calling GetConnectionString at each usage site where an interpolation can creep in.

// Program.cs — one read, one value, no interpolation downstream.
var writer = builder.Configuration.GetConnectionString("OrdersWriter")!;
builder.Services.AddDbContext<OrdersContext>(o => o.UseSqlServer(writer));
builder.Services.AddSingleton(new ReaderConnectionString(
    builder.Configuration.GetConnectionString("OrdersReader")!));

Applying the change needs the same rollout care as any pool-size increase: during a rolling update both the old and new values are live, so the transient total is old_size × surviving + new_size × replacing. If the new value is larger and the budget is tight, set maxSurge: 0 for that deployment.

Weighting two pools by measured traffic The per-process allowance is divided between the writer and reader connection strings in proportion to their measured query share rather than split evenly. Per-process allowance: 20 connections, two connection strings split evenly writer 10 reader 10 measured traffic writes + read-after-write — 70% replica reads — 30% weighted writer 14 reader 6 Splitting evenly gives the writer too little and the reader more than it can use The same total, allocated by measured share, removes queueing on the writer without exceeding the allowance
Two pools drawing on one allowance should be weighted by measured traffic. An even split typically starves the busier one while the other holds connections it never uses.

Validation and Verification

Three checks confirm the change, in ascending order of how long they take.

Immediately after the rollout, confirm the session count matches the arithmetic:

SELECT program_name, count(*) AS sessions
FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND program_name LIKE 'orders-api%'
GROUP BY program_name;
-- expect: writer ≈ 14 × replicas, reader ≈ 6 × replicas, and no third row

A third row, or counts that exceed the product, means a pool you did not account for — usually a dynamically constructed string that survived the change.

Through the next peak, confirm no Timeout expired exceptions appear. If they do at the new ceiling, the derived value is below actual concurrency and the constraint is the allowance rather than the setting.

Across a deliberate scale-out to maxReplicas, confirm the total stays within the allowance and every instance starts cleanly. This is the test the surge term exists for, and running it on a quiet afternoon is considerably cheaper than discovering the result during a traffic spike.

Session count before and after Before the change the session count exceeds the arithmetic because of duplicate pools. After hoisting the connection string and applying the derived ceiling it settles at the expected product. 0 service allowance string hoisted, ceiling derived sessions far above the arithmetic settles at Max Pool Size × replicas The drop is mostly from removing duplicate pools; the derived ceiling is what keeps it there as the fleet scales.
The large step down comes from eliminating pools nobody knew existed. The derived ceiling is what keeps the count predictable afterwards.

Frequently Asked Questions

Is there any reason to leave Max Pool Size at 100?
Only for a service with a dedicated database and no other consumers, where the ceiling is genuinely not a shared resource. Even then it is worth setting explicitly to whatever the database can usefully serve, because the default communicates nothing about intent.
Should Min Pool Size change alongside it?
It should be set to trough concurrency, which is usually a small number and independent of the ceiling. Setting it equal to Max Pool Size holds the full allocation continuously, which on a shared database is exactly the waste the derivation exists to avoid.
Does ApplicationIntent=ReadOnly create a separate pool?
Yes — it is part of the connection string, so a reader string and a writer string are always separate pools. That is intended and useful here, since it lets the two be sized independently, and it is why the arithmetic counts strings rather than processes.
What happens if Max Pool Size is lower than the concurrency the process needs?
Requests queue for up to Connect Timeout and then fail. That is the correct behaviour for a bounded resource, and it is much better than the alternative of an unbounded pool exhausting the server — but it means the timeout value determines how much a shortage costs, which is why it should be short.
Can the value be changed without a restart?
No. The connection string is read at start-up and the pool is keyed on it, so a change requires a new process. That is why applying it needs rollout planning, and why maxSurge: 0 matters when the new value is larger.
Does the pool size need to differ between environments?
The value should, because the allowance and replica count differ. The derivation should not — deriving staging’s value from staging’s own numbers keeps the two consistent in method while allowing them to differ in result, and it means a staging test exercises the same arithmetic.
What if two services share a connection string?
They share a pool only within a process; across processes they are separate pools that happen to be configured identically. What matters for the budget is the total, and two services with the same string are two entries in the inventory, not one.
Should Load Balance Timeout be adjusted?
It controls how long an idle connection lives in the pool before being destroyed, defaulting to zero, which means the provider’s own heuristic applies. Setting it explicitly gives the same control other stacks call idle timeout, and on a shared database a value of a few minutes returns connections that a quiet process is not using.