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

Lantana

Side not; You don't need to Close or Dispose your conncetion, command and reader because using statement handles them automatically. – Imbalance

I know this, that is just another desperate attempt at solving the issue :-) – Lantana

Can you run something like SELECT TOP 1 * from USERS (or any table, really) on the remote database - to find out if it's a problem with the query, or simply a problem connecting to the database? – Blower

I have set changed it to do "select 1" only, and then I do not get the exception. – Lantana

I just noticed, you said you're generating load (around 1500 threads). Your pool size is 100. The simple select is probably instantly returning before you get 100 running in parallel. – Blower

@Rob, have changed the statement to "WAITFOR DELAY '00:00:00:200';Select 1", now I am getting an out memory exception locally. – Lantana

@Lantana What happens with your original query but setting max pool = 2000 – Blower

What does the source code look like that calls the Get()? – Doit

Additionally, have you look at this thread #765601 – Doit

Hi, the Get() method is a normal Web.Api method. I doubt that Jmeter is the problem. If it is not generating enough load, then surely that makes the problem worse for me, since I get a SQL timeout without allot of userload. – Lantana

Try it without the asynch options, as you don't seem to be doing anything else that's too radical. We cant see your stored procedure so we have to assume its ok! – Twinkling

Hi. Unfortunately I can not share the Store Proc code, but something I have noticed is that when the stored procedure throws an error I see the exception happening in the code, when the stored proc does not throw exception all is working 100%. Is there any reason for this ? – Lantana

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.

Gospel

You say "waiting for 15 seconds to get their chance for a connection opening". I think I may be experiencing something similar. Can the connection pool only add one connection at a time, even if you're multi-threading? – Jeanicejeanie

refer c-sharpcorner.com/uploadfile/4d56e1/connection-pooling-ado-net – Gospel