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

Question

I use a cs convenience class to handle my sql queries. It is basic, you instantiate by specifying the db name, and it only manages one command/datareader duo at once.

public static void      Connect     (string DBName)
{
    string connectionString =
        "Server=serverNaaaaaaaaame;Database=" 
        + DBName 
        + ";Integrated Security=SSPI;";
    try
    {
        if (cnn != null)
            cnn.Close ();
        cnn = new SqlConnection (connectionString);
        cnn.Open ();
    }
    catch (Exception e)
    {
        string aaaaaa = e.Message;
    }

}
public void     Command     (string command)
{
    try
    {
        if (drd != null)
            drd.Dispose ();
        if (cmd != null)
            cmd.Dispose ();
        cmd = new SqlCommand (command, cnn);
        //cmd.CommandTimeout = 300;
        drd = cmd.ExecuteReader ();
    }
    catch (Exception e)
    {
        string aaaaaa = e.Message;
    }
}

As is, in the multi-threaded parts of my code, I often get timeouts. It seems to be due to my amount of concurrent connection being too high. (If I enter debug upon timeout and try to execute a query in mssqlsms it hangs until I stop the debug.)

I've been told about the Max Pool Size option to set in the connection string, however I can not see what it affects.

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

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

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

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

Answer 1

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.