ADO.NET Connection Pool concurency issue with SQL

问题:

We are having issues to scale out our ASP.NET Web API on Azure because of our SQL Azure DB. The issue is that our Web/Business SQL Azure DB supports a maximum of 180 concurrent requests. At multiple occasions, we've hit that limit, and the following exception is thrown:

Resource ID : 1. The request limit for the database is 180 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637'

To prevent that to happen, we explicitly set the "Max Pool Size" property of our connectionstring to 180 so that ADO.NET is not able to exceed the number of concurrent requests to the DB. But even after this change we kept receiving the same error. We then realized that it was because we had multiple nodes of our app. Therefore, our idea is to set the "Max Pool Size" to 180 divided by the number of nodes we are using, but that seems nuts to me. It feels like a very inefficient use of resources. If I push that reasoning to the extreme, it means I will never be able to scale my web app to more than 180 nodes if I want to make sure that I never exceed the max number of concurrent requests to the DB.

Is there a better approach to deal with this limitation in a distributed environment?

回答1:

Thanks Tim, I think you're right. There are 2 main design issues in our code. 1st, we do not close the connection fast enough. We let our DI(Autofac) dealing with opening and closing the connection rather than dealing with this on a per-method basis. 2nd, some of our queries are very very badly written on top of EF, which creates an even worse mess. We are completely replacing EF with Dapper so we can gain complete control of our insert/update queries. Once we've achieved that, we should be able to get normal performance for our writes(~30-60ms). Once we have reach that point, I guess this is just pure probabilities that the likelihood of having more than 180 concurrent requests to the DB is low enough to consider it a non-problem. Mind due, inside our repository, I guess we could easily wrap our request inside a retry mechanism that would deal with those rare events. What do you think?