85

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.

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342
  • 11
    if you don't have a problem, leave it as the default. – Mitch Wheat Oct 16 '11 at 12:01
  • 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 ? – Furkan Gözükara Oct 16 '11 at 12:03
  • 4
    The default Connection Pool size of 100 is documented by Microsoft at https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling and likely other places. – Henry Troup Oct 19 '17 at 14:58

3 Answers3

108

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.

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Zaphood
  • 2,509
  • 2
  • 22
  • 21
  • 4
    Is there another way of setting the Max and Min poolsize than via the connectionstring? I would prefer to do it in code... – Jos Oct 23 '12 at 12:06
  • 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? – Pierre Feb 22 '16 at 12:54
  • 5
    @Pierre - 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() – Caleb Seadon Aug 07 '17 at 23:17
  • What is your reference for 100 connections by default? – Mahmoud Moravej Oct 10 '17 at 14:20
  • 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. (https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#adding-connections) Seen 2019-09-14 – Jahaziel Oct 14 '19 at 16:13
  • 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 https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing – Arne Klein Mar 23 '22 at 07:59
15

"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

Laminar
  • 297
  • 2
  • 6
-6

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>
Prashant Rai
  • 15
  • 2