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

75
votes

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.

4
sqlsql-server-2008connection-stringconnection-pooling
if you don't have a problem, leave it as the default. – Mitch Wheat
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 ? – MonsterMMORPG
The default Connection Pool size of 100 is documented by Microsoft at docs.microsoft.com/en-us/dotnet/framework/data/adonet/… and likely other places. – Henry Troup

4 Answers

96
votes

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.

17
votes

"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

0
votes

For Spring yml config:

spring:
  datasource:
    url: ${DB-URL}
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    username: ${DB-USERNAME}
    password: ${DB-PASSWORD}
    hikari:
      auto-commit: true
      maximum-pool-size: 200

or

application-prod.properties file:

spring.datasource.hikari.maximum-pool-size=200
-4
votes

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>