Connection Pooling
Opening a physical connection to SQL Server is slow. Connection pooling makes it fast by reusing connections — as long as you close them.
On this page · 5 min · Beginner
On this page
- In simple words
- Learning objectives
- Explanation
- Code example
- Step by step
- Real-world use
- Common mistakes
- Best practices
- Remember this
- Practice exercise
- Mini quiz
- Related topics
- My notes
- Reading time
- 5 min
- Difficulty
- Beginner
- Updated
- Oct 29, 2026
Related tutorials
In simple words#
Pooling is an auto-rickshaw stand: instead of building a new rickshaw for every trip, you take one waiting at the stand and return it after. Fast — as long as everyone returns them.
- Pool
- Reusable open connections.
- Max Pool Size
- Default 100 connections.
- Leak
- Opened but never closed — the stand runs empty.
Learning objectives#
- Explain how pooling works
- Configure pool size
- Recognise and fix connection leaks
- Avoid fragmenting the pool
Explanation#
SqlClient keeps a pool of open physical connections for each unique connection string. Open borrows one; Close or Dispose returns it.
Defaults: Pooling=True, Min Pool Size=0, Max Pool Size=100. When all 100 are in use, the next Open waits and then throws “Timeout expired… max pool size was reached”.
That error almost always means connections are leaked — opened but never closed — not that you need a bigger pool.
Connection strings must match exactly to share a pool. Building strings per user or per request creates many tiny pools.
Code example: Leak vs no leak#
// ❌ Leaks: if ExecuteScalar throws, Close is never called
SqlConnection leaky = new SqlConnection(cs);
leaky.Open();
var count = new SqlCommand("SELECT COUNT(*) FROM Products", leaky).ExecuteScalar();
leaky.Close();
// ✅ Always returned to the pool
using (var conn = new SqlConnection(cs))
{
conn.Open();
using var cmd = new SqlCommand("SELECT COUNT(*) FROM Products", conn);
count = cmd.ExecuteScalar();
}
// Measuring the benefit
var sw = System.Diagnostics.Stopwatch.StartNew();
for (int i = 0; i < 100; i++) { using var c = new SqlConnection(cs); c.Open(); }
Console.WriteLine(quot;100 opens with pooling: {sw.ElapsedMilliseconds} ms");
100 opens with pooling: 21 ms (the first open is slow; the rest reuse the same physical connection — your numbers will vary)
This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.
Step-by-step explanation#
- The leaky version skips Close when an exception happens.
- using guarantees the connection is returned.
- In the loop only the first Open creates a physical connection; the others reuse it.
Real-world use case#
An API starts failing under load with “max pool size was reached”. A code search finds a repository that never disposes its readers; adding using fixes it without touching the pool size.
Common mistakes#
It delays the failure and puts more load on SQL Server. Fix the missing using statements.
Every Open then creates a new physical connection — far slower.
Best practices#
- Always using / await using
- Keep one identical connection string per database
- Monitor pool usage under load
- Use SqlConnection.ClearAllPools only for special cases such as failover testing
Remember this#
Open = borrow. Dispose = give back.
Max Pool Size default = 100.
“Max pool size reached” = leak, not too small.
Practice exercise#
Your task: Set the minimum pool size to 5 and maximum to 200 in a connection string.
Show solution
Server=localhost;Database=ShopDB;Integrated Security=True;TrustServerCertificate=True;Min Pool Size=5;Max Pool Size=200;
Mini quiz#
When do two SqlConnection objects share the same pool?
Want more? Practise all ADO.NET MCQs · ADO.NET interview questions
My notes#
Saved in this browser.