Fixed-size pre-warmed pool vs dynamic on-demand pooling for bursty SQL Server workloads

Answer

When Min Pool Size is set above zero, the ADO.NET connection pooler retains at least that many open connections after they are first created. Idle connections are not closed by the pooler due to inactivity alone; they remain in the pool until the application domain unloads, the process exits, or ClearPool/ClearAllPools is called.

Confirmed facts

• Pool growth toward Max Pool Size is synchronous and blocking: if all pooled connections are in use and a new request arrives, the pooler creates a new connection on the calling thread (up to Max Pool Size). Under a burst where requests arrive faster than connections are returned, callers wait on the pool’s internal semaphore and experience connection‑establishment latency for each new connection until Max Pool Size is reached.
• The pooler reuses any available idle connection from the pool (FIFO order). A connection is discarded only when: (a) it is detected as broken during GetConnection or via the background health check, (b) Connection Lifetime (seconds) is set and exceeded at the moment the connection is returned to the pool, or (c) the pool is cleared explicitly. There is no background timer that proactively shrinks the pool down to Min Pool Size.

Steps for this case

1. Decide whether you want to keep sessions open during idle periods. If you need zero latency at burst start, set Min Pool Size to the expected baseline (e.g., 5) and pre‑warm by opening and closing that many connections at startup.
2. Set Max Pool Size to a value that your SQL Server can sustain (consider worker threads, login rate, and licensing). Avoid setting it arbitrarily high without server‑side capacity planning.
3. Leave Connection Lifetime at 0 (infinite) unless you have a reason to recycle connections; otherwise, a non‑zero lifetime combined with Min Pool Size > 0 can cause the pool to oscillate and defeat pre‑warming.
4. Monitor server‑side session count (e.g., via sys.dm_exec_sessions) during idle to verify it matches Min Pool Size per pool.

Missing diagnostic detail

If you observe unexpected latency or pool exhaustion, please provide the current Max Pool Size value and any observed wait times (e.g., from ETW PoolGet events) so we can determine whether the client pool or SQL Server resources are the bottleneck.