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.

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.

Answer 2

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

Answer 3

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.