Whether this is SQL Server on-premise or Azure SQL Database, maximum number of requests which can be processed by database engine simultaneously is limited by its compute size or you can say SKU. Service tier is another factor that you need to consider on Azure in addition to compute size as the size of SKU is not directly known in DTU based provisioning. One of the motivations that I received to write this post was, one of my colleagues asked about concurrent request limits. He was performing some load test for his application and reached 200 throttling point on the underlying azure SQL database.
So, let’s look at some quick highlights about such limits on Azure SQL database –
It’s different for Gen4 hardware generations and Gen5 so we will consider only Gen5 generations in this article as it’s most widely and the recommended one to be used
So, what happens when these limits are reached –
1. Query latency is increased or
2. Even queries can get timed out
…and how can you mitigate this –
Not necessarily you need to mitigate this always e.g. if you discovered this during your load testing exercise, this gives you an insight of what the maximum limit of your systems are or knowing this in advance based on your tier selection and configuration helps you setup the expectations right.
However, if it is not as per the expectations then you may do the following to mitigate this –
1. Increase the service tier or compute size of the database
2. Optimize the queries so that they consume less resources and queries are processed faster
3. Implement the connection resiliency to keep on trying on its own by the applications in case they are getting timed out