Active Azure Sql Connections are over the connection pool limit

Question

We fight the issue in production when once in a while our Azure SQL database performance significantly degrades. We know we have locks on one of the tables, but these locks are not deadlocks, they are long locks and in an hour or so the performance returns to normal. We are trying to find all the possible scenarios on how we get these long locks (every query is super fast and all performance analyzers could show us what causes long locks). The reason for this question is the picture below:

Out connection pool settings allow only 200 connections to be pooled. And most of the times we have about 10-20 open/pooled connections with the database. Then suddenly a number of active connections start to grow and the pool is completely taken. While a number of pooled connections stay below 200, we see a number of active connections using sp_who2 reach 1.5k-2k connections (sometimes 4k-5k).

I've built the same chart using Azure Portal monitoring tools. It has different aggregation period but shows the same issue:

the connection string we use:

Data Source=[server].database.windows.net;initial catalog=[database];persist security info=True;user id=[user];password=[password];MultipleActiveResultSets=True;Connection Timeout=30;Max Pool Size=200;Pooling=True;App=[AppName]

How is that possible taking into account connection pool limitation of 200 connections?

ps: there is no periodic task, long running query or other tool doing anything, we checked with sp_who2 all the active connections to the database.

Nope

hi, do you have application insights running on the sql, does this provide the same chart analysis or show any exceptions as dependent on service plan level there is a limit on number of active concurrent connections, i found this during a load test. – Photomultiplier

I don't get this: "While a number of pooled connections stay below 200, we see active connections reach 1.5k-2k connections" what does that mean? Do you have multiple machines connecting to the database? – Sikorski

@MarkWest I've updated my post and added Azure Portal chart showing the same issue. We don't see any exceptions or dependent service failures during that period (no deadlocks, no connection failures). – Nope

Are you sure you are disposing Entity Framework context? Either it has to be wrapped with using or context must be disposed in OnDispose method of controller. – Addict

@SimonMourier I do have several hosts connected to the same database but each host has the same limitation of 200 connections. In the example above you can see a single host metrics (pooled connections) and database overall metrics (total active) – Nope

@AkashKava absolutely, all database operations go within using(var db = new DbContext()) { ... } statement – Nope

@AlexeyStrakh any third party tool using context or anywhere static instance of context is stored? Are you using some DI for mvc controller? May be disposing isn't happening if previous statement fails with an exception in OnDispose of controller or similar item. Is there increase in number of exceptions when number of connections increases? – Addict

@AkashKava I admit that we have somewhere db connection leak but the reason of this question is how this leak could go over connection pool limit? Connection leak detection worth a separate thread and I work on it as well – Nope

Connection pool does not mean it will not create new connection if it reaches limit, it only means that it will not dispose actual connection and it will keep it in pool. If pool size is 200 and all connections are in use, for 201'st connection, new connection will be created. Pool is only retained to avoid connecting/disconnecting, but it is not maximum number of active connections. – Addict

AFAIK, .NET connection pool prevents you from creating a new connection when the limit is reached. It will throw an exception if the limit is reached and a connection couldn't be retrieved from the connection pool (from documentation and from my experience). – Nope

@AkashKava, the documentation (learn.microsoft.com/en-us/dotnet/framework/data/adonet/…) states "If the maximum pool size has been reached and no usable connection is available, the request is queued". That implies no new connections are created. – Paragraphia

Answer 1

[this is more of a long comment than an answer]

I do have several hosts connected to the same database but each host has the same limitation of 200 connections

The connection pool is per (Connection String,AppDomain). Each Server might have multiple AppDomains. And each AppDomain will have one connection pool per connection string. So here if you have different user/password combos, they will generate different connection pools. So no real mystery why it is possible to have more than 200 connections.

So why are you getting lots of connections? Possible causes:

Connection Leaks.

If you are failing to Dispose a DbContext or a SqlConnection that connection will linger on the managed heap until finalized, and not be available for reuse. When a connection pool reaches its limit, new connection request will wait for 30sec for a connection to become available, and fail after that.

You will not see any waits or blocking on the server in this scenario. The sessions will all be idle, not waiting. And there would not be a large number of requests in

select *
from sys.dm_exec_requests 

Note that Session Wait Stats are now live on Azure SQL DB, so it's much easier to see realtime blocking and waits.

select *
from sys.dm_exec_session_wait_stats

Blocking.

If incoming requests begin to be blocked by some transaction, and new requests keep starting, your number of sessions can grow, as new requests get new sessions, start requests and become blocked. Here you would see lots of blocked requests in

select *
from sys.dm_exec_requests

Slow Queries.

If requests were just talking a long time to finish due to resourse availability (CPU, Disk, Log), you could see this. But that's unlikely as your DTU usage is low during this time.

So the next step for you is to see if these connections are active on the server suggesting blocking, or idle on the server suggesting a connection pool problem.

Rimmer

I'm using single connection string, no variation with app names, user, passwords, etc. I've multiplied 200xN where N is a number of app domains accesing the database. It's impossible that I have more than 3 app domains using the same connection string so no more than 600. Again, I'm not trying to find a connection leak or deadlock as part of this question, I'm trying to understand how I can go above 2k connection (sometimes it was 4.5k!) – Nope

Quick answer: Parallelism – Overeager

Connection.OpenAsync() and ExecuteNonQueryAsync() etc. seems to ignore the max pool size property in the connectionstring. – Petrozavodsk

Answer 2

There are 2 things you can check on you dbcontext objects to see if you are using them correctly and dispose object to return the connection to the connection pool.

First, you are creating the dbcontext from code. Check if there is a using statement around each creation scope of the dbcontext object. Something like:

using (var context = new xxxContext()) {
    ...
}

This will dispose the context when it goes out of scope automatically.

Second you are using dependency injection to inject the dbcontext object. Make sure you are using scoped:

services.AddScoped<xxxContext>(

Then the DI will take care of disposing your context objects.

The next thing you can check is if you have uncommitted transactions. Check if all you transactions are within using blocks, so they will commit or rollback when you are out of scope.

Sundried

Answer 3

The problem may related to "Pool fragmentation"

Pool fragmentation is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance.

Pool Fragmentation Due to Integrated Security* Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server. This is a side effect of a particular Web application architecture that developers must weigh against security and auditing requirements.

Source : https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

Riva