Issues with connections to database under low load (dotnet core)

Question

I'm trying to load test a basic API, I've started getting some strange issues coming out of the database connection. I've now narrowed it down to the SQL connection itself. (I'm using SELECT 1 to test connection only)

under very low load (15 calls per second) everything works exactly as expected.

under low load (25 calls per second) the first 4-5 calls come back at an okay speed, then slowing down rapidly. A lot of calls timing out due to no connection in the pool.

under medium load (50 calls per second) everything locks up entirely, nothing comes back. And I start to get strange things like A network-related or instance-specific error occurred while establishing a connection to SQL Server. coming up. Cannot get a connection from the pool again.

exec sp_who2 on the server shows no connections from dotnet either.

To make it worse the only way to recover from this is to bounce the entire service.

I have ruled out the server itself because this is happening on a powerful SQL server on-prem, an azureSql database, and a local service running on docker.

 int selected = 0;
 var timer = Stopwatch.StartNew();
 using (SqlConnection connection = CreateNewConnection())
 {
      try
      {
          connection.Open();
          selected = connection.QueryFirst<int>("SELECT 1");
          timer.Stop();
      }
      catch (Exception e)
      {
          Console.WriteLine("Failed connection");
          Console.WriteLine("fatal    " + e.Message);
          responseBuilder.AddErrors(e);
      }
      finally
      {
          connection.Close();
      }
 }

 responseBuilder.WithResult(new {selected, ms = timer.ElapsedMilliseconds});

I've even tried disposing, and forcing the connection close manually to understand what is going on.

This is running dotnet core, and dapper (I get the same issues even without dapper)

I've also tried upping the max connection pool limit to absurd numbers like 1000, and there was no effect.

edit

After trying a bit more, I decided to try with Postgres. Which works perfectly at over 1k calls per second. Am I missing something on in sql server itself? or on the connection?

Something to point out, These are shotgun calls. So a batch gets fired off as fast as possible, then wait for each request to return after.

Also this is using linux (and environments are docker k8s)

Someone wanted to know how connections got created

    private IDbConnection CreateNewConnection()
    {
        var builder = new SqlConnectionStringBuilder()
        {
            UserID = "sa",
            Password = "012Password!",
            InitialCatalog = "test",
            DataSource = "localhost",
            MultipleActiveResultSets = true,
            MaxPoolSize = 1000
        };
        return new SqlConnection(builder.ConnectionString);
    }

Another note

Not shotgunning (waiting for the previous call to complete, before sending another) seems to have a decent enough throughput. It appears to be something with handling too many requests at the same time

Version Information dotnet 2.1.401 SqlClient 4.5.1

Answer 1

I can verify something fishy is going on but it's probably not pooling. I created a console application and run it both from a Windows console and a WSL console on the same box. This way I was able to run the same code, from the same client but different OS/runtime.

On Windows, each connection took a less than a millisecond even with an absurd 500 DOP :

985 : 00:00:00.0002307
969 : 00:00:00.0002107
987 : 00:00:00.0002270
989 : 00:00:00.0002392

The same code inside WSL would take 8 seconds or more, even with a DOP of 20! Larger DOP values resulted in timeouts. 10 would produce results similar to Windows.

Once I disabled MARS though performance went back to normal :

983 : 00:00:00.0083687
985 : 00:00:00.0083759
987 : 00:00:00.0083971
989 : 00:00:00.0083938
992 : 00:00:00.0084922
991 : 00:00:00.0045206
994 : 00:00:00.0044566

That's still 20 times slower than running on Windows directly but hardly noticable until you check the numbers side by side.

This is the code I used in both cases :

   static void Main(string[] args)
    {
        Console.WriteLine("Starting");

        var options=new ParallelOptions { MaxDegreeOfParallelism = 500 };
        var watch=Stopwatch.StartNew();
        Parallel.For(0,1000,options,Call);
        Console.WriteLine($"Finished in {watch.Elapsed}");
    }

    public static void Call(int i)
    {            
        var watch = Stopwatch.StartNew();

        using (SqlConnection connection = CreateNewConnection())
        {
            try
            {
                connection.Open();
                var cmd=new SqlCommand($"SELECT {i}",connection);
                var selected =cmd.ExecuteScalar();                    
                Console.WriteLine($"{selected} : {watch.Elapsed}");
            }
            catch (Exception e)
            {
                Console.WriteLine($"Ooops!: {e}");
            }
        }
    }

    private static SqlConnection CreateNewConnection()
    {
        var builder = new SqlConnectionStringBuilder()
        {
            UserID = "someUser",
            Password = "somPassword",                        
            InitialCatalog = "tempdb",
            DataSource = @"localhost",
            MultipleActiveResultSets = true,
            Pooling=true //true by default                
            //MaxPoolSize is 100 by default
        };
        return new SqlConnection(builder.ConnectionString);
    }
}