Azure SQL server max pool size was reached error

Question

We have azure sql server, while connecting more than 40 concurrent users we getting below error.

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.

While doing the load test we getting the error.we directly executing a stored procedure using a tool([http://www.datamanipulation.net/sqlquerystress/][1]). For a single user stored procedure will return the result in 1 sec.

Hardware details for azure is Standard SERVICE TIERS and s2(50DTU) PERFORMANCE LEVEL. Database size is 705 MB.

We are restore the same database in local pc. Locally we are using SQL Server 2014 express edition. Using this tool we have connect 200 users successfully.

How to we achieve 200 concurrent users in azure? I would appreciate for your valuable comments.

Answer 1

1. These queries must be running long enough in SQL DB so that the connections are exhausted. Look at the sys.dm_exeC_requests and / or sys.dm_exec_connections to see how many are opened for 40 users test. The slow queries could also be because of the resource limits you are hitting on S2 tier. Look at the sys.resource_stats view to see if you are hitting any resource limits. If that is the case scaling up may help your problem.

2. Make sure you explicitly open and close your connections so that .Net can manage your connections

3. Set Max Pool Size in connection string to larger value