How do .NET SQL connection pools scale in a multi-node environment?

Question

I have some fairly typical SQL calls in an app that look something like this (Dapper typically in the middle), .NET 6:

var connection = new SqlConnection("constring");

using (connection)
{
    await connection.OpenAsync();
    var command = new SqlCommand("sql");
    await command.ExecuteAsync();
    await connection.CloseAsync();
    connection.Dispose();
}

A request to the app probably generates a half-dozen calls like this, usually returning in <0 to 10ms. I almost never see any SQL usage (it's SQL Azure) beyond a high of 5%.

The problem comes when a bot hits the app with 50+ simultaneous requests, coming all within the same 300 or so milliseconds. This causes the classic error

InvalidOperationException: 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

I have the following things in place:

I have the connection string set to a max pool size of 250.
I'm running three nodes as an Azure App Service.
The call stacks are all async.
I do have ARR Affinity on because I'm using SignalR, but I assume the load balancer would spread out the requests as the bot likely isn't sending ARR cookies.
The app services and SQL Server do not break a sweat even with these traffic storms.

Here's the question: How do I scale this? I assume human users don't see this and the connection pool exhaustion heals quickly, but it creates a lot of logging noise. The App Service and SQL Server instance are not at all stressed or working beyond their limits, so it appears it's the connection pool mechanics that are a problem. They're kind of a black box abstraction, but a leaky abstraction since I clearly need to know more about them to make them work right.

System.Data.SqlClient or Microsoft.Data.SqlClient? If you're not already using the latter, I strongly recommend doing so, as it's more actively maintained and especially a lot better when async gets involved (still not perfect, as it's coming from a code base which was heavily biased towards synchronous operations and async-over-sync wrapping). I'm not saying switching to it would solve all your problems, of course. — Jeroen Mostert
– Jeroen Mostert, Commented Jan 15, 2023 at 21:24
IDbConnection, IDbCommand and IDbReader, along with all their implementors, are IDisposable. In other words you need to use using with every SqlConnection, SqlCommand and SqlReader instance or you're going to leak database connections from the pool. — AlwaysLearning
– AlwaysLearning, Commented Jan 15, 2023 at 21:26
@JeroenMostert yes, using the newer bits. @AlwaysLearning I'm using the connection and disposing of it. Commands and readers can't hang on to something that you disposed. — Jeff Putz
– Jeff Putz, Commented Jan 16, 2023 at 15:30
If you don't see at least 250 concurrent active requests (not just connections or sessions) at SQL Server during this condition, then you have a connection leak, and are allowing pooled connections to wait on the GC to be closed. — David Browne - Microsoft
– David Browne - Microsoft, Commented Jan 16, 2023 at 16:00
I do see that many requests, thus my frustration. :) It's not likely a connection leak, all the calls share the same using code. And that's not even my concern. If a node can't handle more than 250 requests at the same time, that's not great. — Jeff Putz
– Jeff Putz, Commented Jan 16, 2023 at 16:06

David Browne - Microsoft · Accepted Answer · 2023-01-16 15:54:01Z

1

Here's the question: How do I scale this?

.NET 6 introduced Rate Limiting, which is really the right solution here. Test how many concurrent requests your API app and database can comfortably handle, and stall or reject additional requests.

Take the analogy of an Emergency Room. Do you want to let everyone into the back who walks in the door? No once all the rooms are full, you make them wait in the waiting room, or send them away.

So put in a request throttle like:

builder.Services.AddRateLimiter(options =>
{
    options.GlobalLimiter = PartitionedRateLimiter.Create<HttpContext, string>(httpContext =>
        RateLimitPartition.GetFixedWindowLimiter(
            partitionKey: httpContext.Request.QueryString.Value!,
            factory: partition => new FixedWindowRateLimiterOptions
            {
                AutoReplenishment = true,
                PermitLimit = 50,
                QueueLimit = 10,
                Window = TimeSpan.FromSeconds(10)
            }));
    options.OnRejected = (context, cancellationToken) =>
    {
        context.HttpContext.Response.StatusCode = StatusCodes.Status429TooManyRequests;
        return new ValueTask();
    };
});

edited Jan 16, 2023 at 15:54

answered Jan 15, 2023 at 22:33

David Browne - Microsoft

90.8k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jeff Putz Over a year ago

Your link is to something from Sharepoint 2010, but I did see this for .NET 7: devblogs.microsoft.com/dotnet/… Still, doesn't feel like a solution, because the problem is how the resources are managed, not exhausting them.

David Browne - Microsoft Over a year ago

Updated with the correct link. And per your description, you've probably got a connection leak, and this isn't the connection pool's normal behavior.

Jeff Putz Over a year ago

Probably not. Every connection created shares the same using code.

David Browne - Microsoft Over a year ago

I've seen connection pool exhaustion not from a connection leak only a handful of times. It's really a rare scenario. In your case you would have 250 active connections, your SQL Server would be very busy, and a thread would have to wait 30 seconds without a connection becoming available. Really a connection leak is the most likely scenario.

Jeff Putz Over a year ago

I'm marking this as "a" solution, but I don't care for it. I can use this to throttle and queue requests, but it seems to me that the connection pooling should do this for me, delaying SQL response until it has free connections. But it looks like it immediately times out the requests when it runs out of connections. Again, app service and SQL stay overwhelmingly underutilized.

|

Collectives™ on Stack Overflow

How do .NET SQL connection pools scale in a multi-node environment?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related