Should I Set Max Pool Size in Database Connection String? What Happens If I Don'T

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

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.

What is the impact of setting the Max Pool Size option in a Sql Connection string in cpp/cli

In multi-threading environment the recommended scenario is

• open SqlConnection (this creates or acquires existing connection in the pool)
• process the data with readers/commands
• close SqlConnection ASAP to release the pool connection for use by other threads)

According MSDN, the MaxPoolSize limits the number of concurrent connections in the pool (for each unique connection string).

Does it change the quantity of SqlConnections I can plug to the same
database?

Yes, when the count of open/used SqlConnection > MaxPoolSize the application is waiting until a pool connection will be freed.

Does it change the amount of concurrent SqlCommand and subsequent
sqlDataReaders I can use at once? On the same SqlConnection? Something
else?

Yes, as it said above, the amount of concurrent connections is limited by a MaxPoolSize.
However, SqlDataReader depends on SqlConnection, you can use (sequentially) several readers at the same connection (see also MARS option for multiple recordsets).

Do I have to specify it in all my connection strings?

Normally, you need only one connection string used for pooling. Otherwise you need to manage multiple pools.

Does it have any effect if the database is already in use in another
soft?

Yes, your queries will affect DBMS performance and even may lock some processing doing by other application at the same database.
However, this problem is not specific of multi-threading.

Restart SQL Server if I change max pool size?

When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created.

http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

Determining what to Raising Max Pool Size to for my Connection Pool

Why is this number defaulted at 100, seems low.

100 connections roughly means that you can handle two hundred 500ms database processings per second on every second without running low on connections. That's quite high.
If you hit this limit, it's time to have a look at optimization.

What are the negatives of significantly raising it to 1000 or something like that?

Is there a good way to determine what this Max Should be raised to?

On a rough basis, it should be the number of connections you will need per second times the average execution time necessary before releasing each connection.
For example, if you need to open one hundred new connections per second, each of them requiring 10 seconds before release (which would be really huge), you would need something like 1000 connections in your pool to handle it on the long run.

What is the "scope" of the Connection Pool?

I would say it is on the AppDomain (will check that)

Is this pool all connections to the database?

Connections are pooled based on connection strings. Any small difference in the connection strings will lead to different pools (though I'm not sure if it is case sensitive)

Each "Machine/Server" connecting has is own Pool?

About your problem, it might be several things :

• your app receives much more traffic than it can handle (SQL profiler should help about that, raising the pool size would then help)
• you have some queries that takes too much time and are called too often (taking several seconds, called several times per second) creating a bottleneck (profiler would help too)
• you leak connections or open too many of them in some nested (recursive) loop
• you have so many pools that you hit the database limit for connections ( 32767 on my 2008R2 Std Select @@MAX_CONNECTIONS ) That seems unlikely

Should connection pool size be same as max_connections?

The connection pool size is the size of always available connections whereas max connections is the maximum of connections.

thoroughly, it makes sense when max connections is bigger then the connection pool size. I would even say this is the standard case.

For example if you have a non regular peak in your application and you need sometimes more connections then regular. These extra connections have an initial overhead for creation but they will not strain your system after they are not needed anymore.

Generally, these settings are a decision about how many resources should be occupied permanently but therefor always available and what is the limit of occupied resources.

What makes no sense is to have a bigger connection pool than max connections.

How can I solve a connection pool problem between ASP.NET and SQL Server?

In most cases connection pooling problems are related to connection leaks. Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.

You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception:

var connection = new SqlConnection(connectionString);

connection.Open();
// some code
connection.Close();                

The correct way would be this:

var connection = new SqlConnection(ConnectionString);

try
{
     connection.Open();
     someCall (connection);
}
finally
{
     connection.Close();                
}

or

using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     someCall(connection);
}

When your function returns a connection from a class method make sure you cache it locally and call its Close method. You'll leak a connection using this code for example:

var command = new OleDbCommand(someUpdateQuery, getConnection());

result = command.ExecuteNonQuery();
connection().Close(); 

The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

If you use SqlDataReader or a OleDbDataReader, close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.

---

This article "Why Does a Connection Pool Overflow?" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:

• Run sp_who or sp_who2. These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
• Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
• Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
• Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.

Why do we need to set Min pool size in ConnectionString

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.

---

Related Topics