Should I set max pool size in database connection string? What happens if I don't?

Question

This is my database connection string. I did not set max pool size until now.

public static string srConnectionString = 
                       "server=localhost;database=mydb;uid=sa;pwd=mypw;";

So currently how many connections does my application support? What is the correct syntax for increasing the connection pool size?

The application is written in C# 4.0.

Fromma

if you don't have a problem, leave it as the default. – Carliecarlile

currently yes but i think it might cause problems at peak moments. So i prefer set higher than default. as i read default is 100 am i right ? – Stitt

The default Connection Pool size of 100 is documented by Microsoft at learn.microsoft.com/en-us/dotnet/framework/data/adonet/… and likely other places. – Noddy

Answer 1

Currently your application support 100 connections in pool. Here is what conn string will look like if you want to increase it to 200:

public static string srConnectionString = 
                "server=localhost;database=mydb;uid=sa;pwd=mypw;Max Pool Size=200;";

You can investigate how many connections with database your application use, by executing sp_who procedure in your database. In most cases default connection pool size will be enough.

Milore

Is there another way of setting the Max and Min poolsize than via the connectionstring? I would prefer to do it in code... – Fado

What happens when setting the 'max pool size' to, let say 30. When all the slots filled with 'in-use' and 'idle connections', will the idle connections be closed automatically to make room for new connections? – Corner

@Corner - you'll receive an error similar to the following: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() – Illfounded

What is your reference for 100 connections by default? – Mouthful

The first paragraph of the "Adding Connections" section of the Documentation article SQL Server Connection Pooling (ADO.NET) states that 100 is the default max pool size. (learn.microsoft.com/en-us/dotnet/framework/data/adonet/…) Seen 2019-09-14 – Overtire

In most cases, you want a smaller rather than bigger pool size, your database only has so many threads that it can use to process your request. By default "Pool Size = # of cores * 2 +1". There is a great explanation for that in the HikariCP documentation github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing – Efflorescence

Answer 2

"currently yes but i think it might cause problems at peak moments" I can confirm, that I had a problem where I got timeouts because of peak requests. After I set the max pool size, the application ran without any problems. IIS 7.5 / ASP.Net

Sworn

This is not an answer – Paraphrast

Answer 3

We can define maximum pool size in following way:

                <pool> 
               <min-pool-size>5</min-pool-size>
                <max-pool-size>200</max-pool-size>
                <prefill>true</prefill>
                <use-strict-min>true</use-strict-min>
                <flush-strategy>IdleConnections</flush-strategy>
                </pool>

Alarum

What file is this? – Afterworld

it is not clear that what file is this, if it is a webconfig then where should we add this <pool> tag. – Hydrastinine