活动的Azure Sql连接超出了连接池限制 [英] Active Azure Sql Connections are over the connection pool limit

问题描述

我们有时会在生产环境中解决该问题，因为我们的Azure SQL数据库性能会显着下降.我们知道我们在其中一个表上有锁，但是这些锁不是死锁，它们是长锁，并且在一个小时左右的时间内性能恢复正常.我们正在尝试找到所有可能的方法以获取这些长锁(每个查询都非常快，并且所有性能分析器都可以向我们显示导致长锁的原因).该问题的原因如下图所示:

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:

输出连接池设置仅允许合并200个连接.在大多数情况下，我们与数据库之间大约有10-20个打开/合并的连接.然后，突然有许多活动的连接开始增长，并完全占用了该池.虽然池化连接的数量保持在200以下，但使用sp_who2的活动连接数量达到了1.5k-2k(有时是4k-5k).

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).

我已经使用Azure Portal监视工具构建了相同的图表.它具有不同的聚集期，但显示相同的问题:

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:

数据源= [服务器] .database.windows.net;初始 目录= [数据库]；持久安全性信息= True；用户 id = [用户];密码= [密码]; MultipleActiveResultSets = True;连接 超时= 30；最大池大小= 200；缓冲=真实； App = [AppName]

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]

如何考虑200个连接的连接池限制?

ps:没有定期任务，长时间运行的查询或其他工具在做什么，我们用sp_who2检查了与数据库的所有活动连接.

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.

推荐答案

[这比答案更冗长，而不是答案]

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

我确实有几台主机连接到同一数据库，但是每台主机具有200个连接的相同限制

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

每个(连接字符串，AppDomain)连接池.每个服务器可能具有多个AppDomain.每个AppDomain每个连接字符串将有一个连接池.因此，如果您有不同的用户/密码组合，它们将生成不同的连接池.因此，没有什么真正的谜，为什么可能有200个以上的连接.

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:

连接泄漏.

如果无法处理DbContext或SqlConnection，则该连接将在托管堆上持续存在，直到最终确定为止，并且不可重用.当连接池达到其限制时，新的连接请求将等待30秒以使连接变为可用，然后失败.

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 

请注意，会话等待状态现在已在Azure SQL DB上显示，因此更容易查看实时阻止和等待.

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

封锁.

如果传入请求开始被某些事务阻止，并且新请求不断启动，则会话数会增加，因为新请求会获得新会话，启动请求并被阻止.在这里，您会在

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

慢查询.

如果由于资源可用性(CPU，磁盘，日志)而导致请求等待很长时间才能完成，则可以看到此消息.但这不太可能，因为这段时间内您的DTU使用率很低.

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.

这篇关于活动的Azure Sql连接超出了连接池限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！