cloud resource_error ai_generated true

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.

ID: cloud/azure-functions-dynamic-sku-sql-connection-pool-exhausted

Also available as: JSON · Markdown · 中文
85%Fix Rate
85%Confidence
1Evidence
2024-06-12First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Azure Functions runtime v4 (.NET 8) active — — —
Azure SQL Database (DTU-based S2 tier) active — — —
System.Data.SqlClient 4.8.6 active — — —

Root Cause

Azure Functions on the Consumption (dynamic) plan scale out rapidly under load, creating many concurrent instances that exhaust the SQL Database connection pool (default max pool size = 100) because each instance opens new connections without reusing them efficiently.

generic

中文

消耗（动态）计划中的 Azure Functions 在负载下快速扩展，创建许多并发实例，这些实例耗尽了 SQL 数据库连接池（默认最大池大小 = 100），因为每个实例都会打开新连接而无法有效重用。

Official Documentation

https://docs.microsoft.com/en-us/azure/azure-functions/manage-connections

Workarounds

  1. 85% success Increase the Max Pool Size in the connection string to accommodate more concurrent connections (e.g., add 'Max Pool Size=200'): `Server=tcp:myserver.database.windows.net;Database=mydb;User Id=myuser;Password=mypwd;Max Pool Size=200;` 
    Increase the Max Pool Size in the connection string to accommodate more concurrent connections (e.g., add 'Max Pool Size=200'): `Server=tcp:myserver.database.windows.net;Database=mydb;User Id=myuser;Password=mypwd;Max Pool Size=200;`
  2. 80% success Implement connection multiplexing by using a singleton HttpClient or Dapper with a shared DbConnection in the Azure Functions startup class to reuse connections across invocations. 
    Implement connection multiplexing by using a singleton HttpClient or Dapper with a shared DbConnection in the Azure Functions startup class to reuse connections across invocations.

中文步骤

  1. Increase the Max Pool Size in the connection string to accommodate more concurrent connections (e.g., add 'Max Pool Size=200'): `Server=tcp:myserver.database.windows.net;Database=mydb;User Id=myuser;Password=mypwd;Max Pool Size=200;`
  2. Implement connection multiplexing by using a singleton HttpClient or Dapper with a shared DbConnection in the Azure Functions startup class to reuse connections across invocations.

Dead Ends

Common approaches that don't work:

  1. 60% fail

    The issue is connection pooling, not the SQL query itself; optimizing a single query won't reduce the number of concurrent connections.

  2. 85% fail

    The function timeout is separate from the SQL connection timeout; increasing it does not affect pool exhaustion.

  3. 40% fail

    Scaling up to a higher SKU may help but is often unnecessary and costly; the root cause is connection management, not database capacity.