Why do we need to set Min pool size in ConnectionString

Question

For SQL connection pool, why do we need to set up a min pool size? As connections will be saved in the connection pool and reused, why do we need to keep live connections specified by the min pool size? Thanks.

Giannagianni

Why do you need? If you don't specify the min size it's 0. – Hugely

@TimSchmelter i guess he wants to know what do we need it at all? Is there any purpose of having this? – Look

@TimSchmelter yes my question is what is purpose of having min pool size – Giannagianni

Answer 1

Opening and maintaining connections is expensive, so if you know that you need multiple connections (always) it's better to specify the MinPoolSize because then it's ensured that these connections are available.

Also, from MSDN:

If MinPoolSize is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools involves minimal system overhead.

Hugely

Thanks Tim! How did you find this? I searched many places on web, but didn't find this great specification. – Giannagianni

I've just looked at the MSDN page and searched for MinPoolSize. – Hugely

Answer 2

why do we need to keep live connections specified by the min pool size

As you may know that connection creation is resource intensive job. So, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won't have to wait for those database connections to establish. You can read the details of pooling here.

Look

Thanks. So, if I turn the connection pooling on, but min pool size 0, connections can reuse after a short while, but will be closed after a long period. If I set min pool size to 5, so at least 5 connections will be kept open for ever? Am I right? Thanks – Giannagianni