CODEWITHSUNDEEP
sql-serverentity-frameworkazureazure-sql-database
James Mundy
James Mundy

Reputation: 4329

EntityCommandExecutionException: Azure SQL with Entity Framework slow and rejecting requests

Over the past few days we've started to get intermittent exceptions thrown by Entity Framework communicating with our Azure SQL database. The exception it throws is related to our code in particular but the messages are:

An error occurred while executing the command definition. See the inner exception for details. Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The wait operation timed out

Clearly the request to the database has timed out but it started happening all of a sudden and hasn't happened before. In the most recent days we have seen average response time increase as well: Azure SQL Response time The best response times aren't exactly that fast as they need some refinement and optimisation but you can see a marked increase.

Our mobile app requests lots of information from our API on start and makes a number of requests which all seem to fail together, after a few minutes making these requests individually works just fine.

Any ideas as to what might be happening here? There are no errors in the Azure Portal except notifications that our API is responding slower than it would normally (which we know!)

Upvotes: 1

Views: 509

Answers (1)

James Mundy
James Mundy

Reputation: 4329

This is, annoyingly, the second time I have been caught out by this issue so was worthy of a post.

It is a result of your database tier and the DTU limit that Azure gives you.

A DTU is a unit of measure for the performance of a service tier and is a summary of several database characteristics. Each service tier has a certain number of DTUs assigned to it as an easy way to compare the performance level of one tier versus another. From: Azure SQL Database "DTU percentage" metric

The clue as to what was happening can be found here:

When your workload exceeds the amount of any of these resources, your throughput is throttled - resulting in slower performance and timeouts.

We were using the basic tier database so our limit was 5 DTU and we were requesting lots of data all at once (admittedly too much) when the app starts and hitting this cap. Azure SQl was throttling our queries, slowing some down and rejecting others. Remembering something like this before, I had checked the DTU graphs in the Azure Portal but I must have been looking at a longer time scale so the big spikes in usage were hidden to me.

We solved this, for now, by increasing our Azure database tier and the DTU limit from 5 to 20 (4x performance) which stopped all exceptions and failed requests.

This is a particularly annoying issue due to the vague exceptions provided by EntityFramework and the slow requests. It would be good for Azure SQL to include some information about DTU caps in future.

Another thing we added to prevent this was an alert which will notify us in future if our DTU usage ever creeps above 80% again. See Azure Portal > AzureSQL Database > Monitoring > Alert Rules. Azure DTU cap alert

In my opinion Azure should create this alert automatically, I'm sure it can't just be me that has been burnt by this!

Upvotes: 3

Related Questions