Max Connection Pool capped at 100

  • I'm running SQL Server 2008 R2 SP1, on a Windows Server 2008 box. I have a .NET script running from Visual Studio 2010 that does the following:

    • Reaches into the database
    • Makes a change
    • Iterates

    The total number of times it will iterate is 150, however it is stopping at 100 connections and I can't figure out why. I could adjust my script to just use a single thread, but I'd prefer to know where I'm missing a max connection setting as that will be more useful to know for future reference.

    Here's where I've checked so far:

    • SQL Connection String in Visual Studio 2010 (it's set to 1000)
    • SSMS Database instance connection properties (it's set to 0 [infinity] user connections)
    • Googled some information on Server 2008, it looks like it can handle more than 100 connections
    • Stepped through my code alongside SP_WHO2 which gives more information on logical connections, seeing that the # of connections starts at 52 and the script errors with the "Max Pooled Connections reached" error at 152 logical connections.
    • Changed the connection string to use Data Source=PerfSQL02;Initial Catalog=Masked;Integrated Security=True;Max Pool Size=1000

    I'm not sure where else to check, I know I have a lot of moving parts here but I'm getting the feeling I'm just missing a max pool setting somewhere.

    Can you post your connection string? Please see my answer. That should be your remedy (this value will be specified in the connection string. `System.Data.SqlClient` defaults to 100, which is why you are see exhaustion of the connection pool).

  • Mike Fal

    Mike Fal Correct answer

    7 years ago

    SQL Server allows a maximum of 32767 connections by default. It can be altered using sp_configure. To view your current configuration for this setting, use the following query:

    select * from sys.configurations
    where name ='user connections'
    

    By default, you should see a maximum of 32767, value_in_use equal to 0 (use the default settings). If this has been altered, you can reconfigure SQL Server to use other values as described in the link.

    You should also review how many connections are actually being made, as there might be more activity outside your application (or your application is making more connections than you think). You will want to look at General Statistics -> Logical Connections in either perfmon or query the values in sys.dm_os_performance_counters (cntr_value will show the current point in time value):

    select * from sys.dm_os_performance_counters
    where counter_name ='User Connections'
    

    Yep, I already checked the user connections (it's the first thing I went to, and is the easiest to find both in UI and in Microsoft's documentation. I also stepped through my code and kept an eye on the number of logical connections via SSMS by using SP_WHO2, which gives a decent amount of information about the logical connections. Idling, my server has 51 connections. When the script fails, it has build 100 more connections. That's how I've gotten to where I am now.

    I clarified my question a bit more with steps I've taken. It might not be a setting in SQL at all, which is why I started looking at Windows and Visual Studio settings.

    If you've checked all that and it still fails at 100 connections, the answer lies outside of the database settings. FYI, those 51 connections are all the system processes (+1 for yourself).

    Just to provide some closure, that last comment ended up being correct. I took a closer look at why those 100 connections were being created and found a leak in the code. After fixing that it works fine. The above information is all still really useful though, and I hope others will find it useful as well.

  • In your connection string, specify max pool size=<your desired max pool size>. So in other words, if you want to change max pool size to the value 500, your connection string could resemble this:

    "data source=your_server; initial catalog=your_db; trusted_connection=true; max pool size=500"
    

    Obviously I'm assuming so much with your other parameters, but this should give you a good idea how to proceed. Connection pooling is client-side provider enforcement. It's the client that will need to specify this max pool size setting, via the connection string.

    Also, make sure you are closing or disposing of your connections properly, otherwise you will tie up connections. Something like this (C#):

    string connectionString = "data source=your_server; initial catalog=your_db; trusted_connection=true; max pool size=500";
    
    using (SqlConnection dbConn = new SqlConnection(connectionString))
    {
        using (SqlCommand sqlCmd = new SqlCommand())
        {
            sqlCmd.Connection = dbConn;
            sqlCmd.CommandText = "select 1;";
    
            // ... so on and so forth
        }
    }
    

    The using block (in C#) calls IDisposable.Dispose() upon completion. You could also implement SqlConnection.Dispose() or SqlConnection.Close() in the finally block of a try/catch/finally block.

    Reference: MSDN Documentation on SqlConnection.ConnectionString property

    Here is the connection string, updating my question too. `Data Source=PerfSQL02;Initial Catalog=Masked;Integrated Security=True;Max Pool Size=1000`

    Is that your connection string *everywhere*? The reason I ask, is because different connection strings are going to be different connection pools.

    That is a very, very good question. I checked 3 connection strings so far, but they're all pushing for a high max pool. I'll see if I can find any other string that might cause it.

    If you just debug the execution, you should be able to look at what the runtime value of `SqlConnection.ConnectionString` is for the particular operation. That'd be the easiest way. If `max pool size` isn't there, it's 100 then.

    This should be marked as the accepted answer as it actually answers the OP correctly. He mentions both the client conn string Max Pool Size parameter and the need to close/dispose connections.

    @AdamCaviness I agree. Who has permissions to change the accepted answer?

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM