Azure SQL Database Connectivity Issues - Too many connections?

Question

I have a site which is a white label (Multiple versions of the same site) which I've launched recently. There isn't a great deal of traffic yet - mainly bots but probably 800 users per day. It is hosted on Azure with an Azure database in addition to an admin panel located on a non-azure server. Both sites connect to the same Azure database. There are also some worker roles running to process data - 99% of the time they aren't doing anything, but they check regularly.

I have always experienced random errors which last a few seconds and then it's ok again, such as:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

This morning, however, we had a more serious problem. It started with:

System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host

This occurred whilst bots (Google, Baidu, AhrefsBot & Wiseguys.nl) were indexing the site. I got one or more errors from these. Then I got:

System.Data.SqlClient.SqlException: The service has encountered an error processing your request. Please try again. Error code 40143. A severe error occurred on the current command. The results, if any, should be discarded.

This was during an ExecuteReader phase.

10 minutes later, the real problem came - which meant that nobody could log in to the admin interface, but the Azure hosted website appeared ok when I tested it although the bots were still bringing up errors. The problem was:

System.ComponentModel.Win32Exception: The wait operation timed out

This continued with random connections working on and off for about an hour. Then I hit another problem:

System.Data.SqlClient.SqlException: Resource ID : 1. The request limit for the database is 180 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance.

This occurred on and off for the last hour - predominantly for the worker roles. I then tried to find out what was taking up all of these requests and I found this command:

SELECT * FROM sys.dm_exec_requests

It only returned 1 or 2 requests when I was running it over and over.

So my questions are: 1) Does anyone else experience relatively regular (once, maybe twice a day) a temporary disconnect from the server hosted on Azure? 2) Does the list of events above indicate a particular problem? This could all have occurred when lots of admins were logging in at once. 3) How can I better debug the number of requests to the database when I get the 180 limit message?

Thanks in advance.

Answer 1

I wrote this question a couple of years ago and got notified of a minor change to the title. Having experienced more of Azure SQL Databases, I do now know the answer to this problem. For the benefit of others, it is simply that your database is set to a tier that is too low.

Azure has pricing tiers that have quite dramatic differences in performance. In order to achieve that, they throttle a lot of performance metrics, e.g. CPU power, requests per minute, etc.

This means that if you're pushing over your tier, your requests will start getting queued up as the CPU power / volume of requests is too high to process. This results in timeouts and then the request limit grows as requests wait to be processed. Eventually, it gets to the point where the database essentially goes down.

My experience is that the lower database levels, such as S0 and S1, are really under-powered and shouldn't be used for anything other than development or very basic sites.

There are some great tools in the Azure portal that allow you to debug what is going on with your database, such as the CPU graphs, index advisor and query performance insights.