Trouble with Azure SQL Database Connection Pooling

Hello everyone,

I'm encountering an issue with connection pooling in Azure SQL Database. I'm using ADO.NET with the latest SQL client library. We've configured connection pooling on our application servers, but we're seeing intermittent `Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. You may need to allow more time for this operation to complete.` errors.

Our connection string includes `Pooling=true;Max Pool Size=200;Min Pool Size=10;`. We are creating and disposing of connections correctly. The application is running on Azure App Service. Is there anything specific to Azure SQL Database that might affect connection pooling or any recommended settings?

Thanks in advance for any help!

Hi SQLMaster123,

Thank you for reaching out. Azure SQL Database generally works well with standard connection pooling.

A few things to check:
1. **DTU/vCore Limits:** Ensure your Azure SQL Database tier isn't hitting its resource limits (DTUs or vCores), as this can cause query delays and timeouts. Check the Azure portal metrics.
2. **Network Latency:** While usually minimal, high latency between your App Service and the SQL Database could contribute. Ensure they are in the same region if possible.
3. **Connection Lifetime:** Consider setting a `Connect Timeout` in your connection string (e.g., `Connect Timeout=30;`) and ensure `Connection Lifetime` is managed if you're explicitly setting it, to avoid prematurely closing connections that might still be in the pool.
4. **App Service Throttling:** Although less common for SQL connectivity, ensure your App Service plan itself isn't being throttled.

Could you provide more details on your Azure SQL Database tier and any recent changes to your application or infrastructure?

Thanks AzureSupportBot!

We're on a General Purpose (4 vCores) tier. Metrics look okay, no obvious throttling or exhaustion there. App Service is in the same region. We haven't explicitly set `Connection Lifetime`. I'll try adding `Connect Timeout=30;` to see if that makes a difference. It's strange that it's intermittent.

@SQLMaster123 Have you considered the `Enlist=false` parameter? Sometimes explicit enlistment in distributed transactions can interfere with pooling if not handled carefully. Also, check if your application is keeping connections open longer than necessary. A common pattern is to wrap the connection usage in a `using` statement (or equivalent) to ensure disposal.

Also, consider your `Max Pool Size`. While 200 seems high, ensure it's not causing contention if you have many threads competing for connections simultaneously. Sometimes a slightly smaller pool size with faster turnover can be more efficient.

@DatabaseNinja Thanks! We are indeed using `using` statements religiously. `Enlist=false` is not currently set. I'll investigate the `Max Pool Size` again and monitor the pooling stats more closely. The `Connect Timeout=30;` addition didn't seem to resolve the core issue.