Database Maximum pool connection size

Question

I have read a few articles on Connection pool which gave me the basic knowledge as to how it works but i have a few doubts that i need some help with.

Connection pool's syntax according to me

According to me here is the implementation of connection pool in sql connection string is: -

Data Source=.\SQLEXPRESS;Initial Catalog=DBName;User Id=sa;password=123456789;MultipleActiveResultSets = True;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;"

How does maximum pool size work?

• Does it transfer every closed connection to the connection pool?
  
  • If so, then will it maintain 100 such closed connections because of Max Pool Size=100?
  • Will these closed 100 connections in the connection pool still count as active connection to the SQL Server?
  • If so, then won't the SQL Server run out of connections if multiple users try to connect it?
• When is connection pool cleared?
  • Does it clear automatically or we have to manually clear it?

It will be extremely helpful if you can help me with the above question or guide me to an informative article or a video.

Answer 1

ADO.NET connection pooling is enabled by default and default connection pool settings (e.g. Max Pool Size=100) are adequate for most applicatitons. See the SqlConnection.ConnectionString and ADO.NET connection pooling documentation for the authoritative answer and details.

In summary, connections are returned to the pool when closed/disposed. These unused pooled connections are not technically active in SQL Server but are in sleeping status pending reuse. Pooled connections that are not reused after an idle period (couple of minutes) are physically closed to reduce resource on both server and client. The connection pooler does this housekeeping automatically.

The best practice in .NET applications is to employ using blocks to ensure connections are returned to the pool after use without the need to explicitly close them.