Sql Connection Pool timeout

Question

[Disclaimer] : I think I have read every stackoverflow post about this already

I have been breaking my head over this for quite some time now. I am getting the following exception in my asp.net web.api.

Exception thrown: 'System.InvalidOperationException' in mscorlib.dll

Additional information: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Most people suggested that I should look for leaked connections in my application. Here is my code. Now I am sure that I am not leaking any connections

public async Task<IEnumerable<string>> Get()
    {
        var ds = new DataSet();
        var constring = "Data Source=xxx;Initial Catalog=xxx;User Id=xxx;Password=xxx;Max Pool Size=100";
        var asyncConnectionString = new SqlConnectionStringBuilder(constring)
        {
            AsynchronousProcessing = true
        }.ToString();


        using (var con = new SqlConnection(asyncConnectionString))
        using (var cmd = new SqlCommand("[dbo].[xxx]", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@x1", 1);
                cmd.Parameters.AddWithValue("@x2", "something");

                await con.OpenAsync();
                using (var rdr =await  cmd.ExecuteReaderAsync())
                {
                    if (rdr.HasRows)
                    {
                        ds.Load(rdr, LoadOption.OverwriteChanges, "MyTable");
                    }
                    rdr.Close();
                    con.Close();
                    ds.Dispose();
                }
            }
        //I know this looks wrong, just an empty api method to show the code
        return new string[] { "value1", "value2" };
    }

The exception does not occur when I am using my local Sql Server. Only happens when I connect to our 'test server'. Are there anything else I can look at when trying resolve this issue. Like Sql server settings / network settings etc.

The stored procedure I call does not lock up the db I have checked for that as well. If that was the case it should have failed on my local Sql instance as well.

I am using jmeter to generate load, 1500 - threads(users). Surely I should be able to handle way more than that.

Thanks in advance

Answer 1

You have not specified any Connection Time out property, so it's 15 seconds default. using Max Pool Size=100 is not a good idea until you don't have proper hardware resources.

You started 1500 threads, so it seems that the some the threads keep waiting for 15 seconds to get their chance for connection opening. And as time goes out, you get the connection time out error.

So I think increasing the 'Connection Timeout' property in connection string may resolve your issue.