Connection pool problem between .net core running on Azure Linux ASP and Azure SQL Database

Question

So we are looking to move our solution from Windows to Linux ASP. The app is behind Azure Front Door and the same code is working perfectly fine on windows ASP with all the defaults. However when deployed to Linux ASP results in very high response time. Tried to change the health checks protocols (HEAD to Get) but no luck (the path for the checks is just /). When we remove the app from AFD it is working just fine, but I guess we are not able to reproduce high load. The db is not loaded at all and it is S2. The logs show the following:

*

    Microsoft.EntityFrameworkCore.Database.Connection[20004]
    An error occurred using the connection to database 'blabla' on server 'blablabla.database.windows.net'.
    Microsoft.EntityFrameworkCore.Query[10100]
    An exception occurred while iterating over the results of a query for context type 'blablabla'.
    System.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.
    at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
    at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
    at Microsoft.Data.SqlClient.SqlConnection.Open()
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
    at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
    at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
[40m[1m[33mwarn[39m[22m[49m: Microsoft.AspNetCore.Server.Kestrel[22]
 Heartbeat took longer than "00:00:01" at ***. This could be caused by thread pool starvation.
[40m[1m[33mwarn[39m[22m[49m: Microsoft.AspNetCore.Server.Kestrel[22]
Heartbeat took longer than "00:00:01" at ***. This could be caused by thread pool starvation.
[40m[1m[33mwarn[39m[22m[49m: Microsoft.AspNetCore.Server.Kestrel[22]
Heartbeat took longer than "00:00:01" at ***. This could be caused by thread pool starvation.

Wondered shall we have to use different SQL connection driver? This is App Service and I believe this is handled by the platform itself.

Thank you!

Are you disposing the connection correctly with a using block? — Charlieface

Randy Torres Randy Torres · Accepted Answer · 2021-03-16T21:46:12

Seems like you are not disposing of the connection correctly or you are doing elastic queries. We experienced a similar issue due to an elastic query across "shards".

This varies on your DB setup so try referencing this link on what an elastic query is. https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-query-overview Essentially should not be querying across multiple shards from within one database as it opens up a connection across all the other shards.

Connection pool problem between .net core running on Azure Linux ASP and Azure SQL Database

1 Answers