Max Connection Pool capped at 100

Question

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.

Answer 1

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'

Answer 2

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