SQL Server Connection Pooling in .NET: How It Works and How to Fix Pool Timeouts

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool." If you have seen this error in a .NET application, the database is usually fine; the application has run out of pooled connections. Understanding how pooling works makes it easy to find the real cause.

SQL Server connection pooling in .NET

How connection pooling works

Opening a physical connection to SQL Server is expensive: network round trips, authentication and session setup. So Microsoft.Data.SqlClient keeps a pool of open connections and reuses them.

  • When you call Open(), you get an existing connection from the pool if one is free.
  • When you call Close() or Dispose(), the connection goes back to the pool instead of being closed.
  • There is a separate pool for each distinct connection string in each process.
  • By default a pool holds up to 100 connections (Max Pool Size). When all 100 are in use, the next Open() waits, and after the connect timeout (15 seconds by default) throws the error above.

Cause 1: connections that are never returned

The most common cause is a leak: code that opens a connection and does not dispose it, especially on an error path.

// Leaks a connection if ExecuteReader throws or the method returns early
var conn = new SqlConnection(connStr);
conn.Open();
var reader = new SqlCommand(sql, conn).ExecuteReader();

// Correct: always returned to the pool, even on exceptions
await using var conn2 = new SqlConnection(connStr);
await conn2.OpenAsync();
await using var cmd = new SqlCommand(sql, conn2);
await using var reader2 = await cmd.ExecuteReaderAsync();

Rules: open as late as possible, close as early as possible, and always use using (or await using). In EF Core, register the DbContext with the normal scoped lifetime so it is disposed at the end of each request.

Cause 2: slow queries hold connections too long

A connection is "in use" for as long as your query runs. If a query that normally takes 50 ms starts taking 10 seconds, perhaps because of blocking or a bad plan, each request holds its connection 200 times longer, and the pool runs dry under normal traffic. In that case the fix is the query, not the pool.

Cause 3: sync over async and thread starvation

Blocking on async code (.Result or .Wait()) in ASP.NET can starve the thread pool, so connections stay checked out while threads wait. Use async/await all the way through.

Cause 4: many different connection strings

Pools are keyed on the exact connection string text. Building a different string per user, or with values in a different order, creates many small pools and many physical connections. Keep one consistent connection string per database and use application-level logic for per-user behaviour.

See what the application is holding

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

Set Application Name=MyApi in the connection string so program_name identifies your app. Many sleeping sessions from one host, stuck near your max pool size, usually mean a leak. Many running or suspended sessions mean slow or blocked queries.

Should you increase Max Pool Size?

Sometimes, for genuinely high-concurrency services, a larger pool is right. But raising it to hide a leak or slow queries just moves the problem to SQL Server, which then handles hundreds of extra sessions. Fix leaks and slow queries first; then size the pool from measured peak concurrency.

Server=myserver;Database=Payroll;Application Name=PayrollApi;
Max Pool Size=200;Min Pool Size=10;Connect Timeout=30;

Quick checklist

  • Every connection, command and reader in using blocks.
  • Async all the way; no .Result or .Wait().
  • One consistent connection string, with an Application Name.
  • Check sessions per host and status when timeouts start.
  • Fix slow or blocked queries before raising the pool size.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more