With Microsoft.Data.SqlClient in an ASP.NET Core app, what do new SqlConnection(cs), OpenAsync and DisposeAsync actually do when pooling is on?
answer
- constructing is cheap, opening borrows
- one pool per exact connection string
- Max Pool Size 100, Min Pool Size 0
- Connect Timeout 15 s covers the pool wait
- dispose returns, never disconnects
basics
~20 sConstructing a SqlConnection allocates only an object. OpenAsync borrows an idle physical connection from the pool for that exact connection string, creating one only if none is free, and DisposeAsync returns it to the pool rather than closing it.
solid answer
~50 s`new SqlConnection(cs)` just parses the string; nothing touches the network. `OpenAsync()` looks up the pool for that exact connection string (and credential), takes an idle physical connection, and opens a new one only when none is idle and the pool is below `Max Pool Size`, which defaults to 100. If all 100 are busy, it waits up to `Connect Timeout` (15 s by default) and then throws `InvalidOperationException` ("Timeout expired... prior to obtaining a connection from the pool"). `DisposeAsync()` or `CloseAsync()` hands the physical connection back; the server session stays open and is reset when the next borrower uses it. So in a web app I open per unit of work, as late as possible, inside `await using`, and keep the connection string identical everywhere, because every textual difference creates a separate pool. Npgsql works the same way, except the pool lives in its `NpgsqlDataSource`.
go deeper
Know that opening and disposing a pooled connection is cheap, and that await using returns it to the pool rather than closing it.
Explain the three calls, what keys a pool, and the defaults: Max Pool Size 100, Min Pool Size 0, Connect Timeout 15 seconds.
Reason about hold time versus open count, and spot pool fragmentation from differing connection strings, including EF Core 10's injected Application Name.
Decide where pool limits are configured and reviewed, and how connection-string construction is centralised so every component shares the intended pool.
## The three calls ADO.NET drivers separate the cheap **connection object** your code holds from the expensive **physical connection** (a TCP session, a login and a server-side session). With **Microsoft.Data.SqlClient**, `Pooling=true` is the default, so the three calls do this: 1. `new SqlConnection(cs)` parses the connection string and allocates an object. No network traffic. 2. `await conn.OpenAsync()` finds the pool for that connection string and borrows an idle physical connection. Only if none is idle and the pool is below its maximum does it log in to SQL Server and create a new one. 3. `await conn.DisposeAsync()` (or `CloseAsync()`) returns the physical connection to the pool. The server session stays alive and is flagged for reset, and the reset is sent ahead of the next borrower's first command, so session state such as temporary tables does not leak between requests. Because of step 3, "open a connection per query" is cheap in .NET; the pool makes it so. ## What identifies a pool SqlClient keys each pool on the **connection string text** together with the credential or access token it carries. Two strings that differ in any way, keyword order and spacing included, get two separate pools. Consequences in a web app: - build the connection string once, from configuration, and reuse it; - avoid per-user credentials in the string, which give every user a private pool; - know that some libraries rewrite the string: EF Core 10 injects an `Application Name` into SQL Server strings that lack one, so EF Core and hand-written ADO.NET on the same database end up in different pools unless you set `Application Name` yourself. ## The settings and their defaults | Behaviour | SqlClient keyword and default | Npgsql keyword and default | |---|---|---| | Pooling switch | `Pooling=true` | `Pooling=true` | | Upper bound | `Max Pool Size=100` | `Maximum Pool Size=100` | | Connections kept warm | `Min Pool Size=0` | `Minimum Pool Size=0` | | How long Open waits | `Connect Timeout=15` (seconds) | `Timeout=15` (seconds) | When the pool is at its upper bound and every connection is in use, `OpenAsync` queues. If none comes back within the timeout, SqlClient throws `InvalidOperationException` with the message "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool...", and Npgsql throws an `NpgsqlException` saying the pool has been exhausted. ## Why web code opens late and disposes early An ASP.NET Core app serves many requests at once, and all of them draw from the same pool per connection string. The time a request **holds** a connection, not the number of times it opens one, decides how many requests the pool can serve. Hence: - wrap the connection, command and reader in `await using`, so exceptions return the connection too; - open just before the first statement and dispose right after the last; - do not keep a `SqlConnection` in a singleton or a static field; a connection object is not thread-safe and would pin one physical connection for the life of the app. ```csharp public sealed class TariffReader(IConfiguration config) { private readonly string _cs = config.GetConnectionString("Meters")!; public async Task<decimal?> PriceAsync(int tariffId) { await using var conn = new SqlConnection(_cs); await conn.OpenAsync(); await using var cmd = new SqlCommand("SELECT price_per_kwh FROM tariffs WHERE id = @id", conn); cmd.Parameters.AddWithValue("@id", tariffId); return (decimal?)await cmd.ExecuteScalarAsync(); } } ``` ## Npgsql: the same idea, owned by a data source Npgsql pools the same way, but the pool belongs to an `NpgsqlDataSource`. Registering one with `AddNpgsqlDataSource` makes the pool explicit and shared; `new NpgsqlConnection(cs)` still pools, through an internal data source the driver keeps per connection string. ## Where the pool lives in an ASP.NET Core process The pool is an in-memory structure inside the driver, so it is **per process and per connection string**: - each scaled-out instance of the API has its own pools, and their maximums add up on the database server; - a restart or redeploy throws every pooled connection away, so the first requests afterwards pay full logins again; - two apps on one host never share a pool, even with identical strings; - `Max Pool Size` is therefore a per-instance number: four instances at 100 can open 400 connections to one database. ## Tools for odd cases - `SqlConnection.ClearPool(conn)` and `SqlConnection.ClearAllPools()` discard pooled connections, for example after a failover left dead sessions in the pool. - `Pooling=false` makes every open a full login; it is a debugging aid, not a production setting.
- Two services in the energy-meter API build their SqlClient connection strings separately, one with an extra space after a semicolon. What happens to pooling?SqlClient keys pools on the connection string text, so the two strings get two independent pools of up to 100 connections each. Nothing fails, but the app can open twice as many connections to the server as intended, and per-pool settings drift. Build the string once from configuration and share it, or share one `DbDataSource`.
- Why do pooled connections not leak temporary tables or SET options from one request into the next?SqlClient flags a returned connection for reset, and the reset request travels ahead of the next borrower's first command, so session-level state from the previous borrower is cleared. That is also why reuse is cheap but not free: the reset costs a little work on the next use, not at dispose.
A connection pool works like a bike-share dock outside an office. Taking a bike (OpenAsync) is quick because the bikes are already there; returning it (DisposeAsync) docks it for the next rider rather than scrapping it. If every bike is out, newcomers wait at the dock, and after a while they give up (the open timeout).
- new SqlConnection(cs) opens a network connection to SQL Server immediately.
- Disposing a SqlConnection closes the TCP session, so pooling needs connections kept open.
- Max Pool Size is unlimited by default, so the pool can never run out.
- One pool is shared by every connection string that targets the same server.
- Keep one SqlConnection in a singleton to avoid the cost of opening connections.