CODEWITHSUNDEEP
azureazure-sql-database
Francesco
Francesco

Reputation: 43

Recurring Timeout on Sql-Azure

On our system, which is implemented by a web role that uses a database sql-azure, we are experiencing recurring timeout on a specific query.

These timeouts occur for a few hours during the day and then do not show up anymore.

The query has two tables with a number of rows is not very high (about 800,000 rows) with joins using primary keys.

The execution plan is ok, the indexes are used properly, the query normally takes two seconds to be performed.

Tests without EntityFramework give the same result.

Transient fault handling are not applicable in the case of timeout.

What can be the cause of this behavior?

Upvotes: 4

Views: 6007

Answers (1)

Luke Merrett
Luke Merrett

Reputation: 5824

We have experienced similar issues in the past using SQL Azure; frequently queries running against tables with less that 10 rows and even the standard .Net membership provider queries, all failed intermittently with timeouts. This is usually when we have little to no activity on our service; mostly at night.

In commonly used areas where it is safe to retry on SQL Timeout (Usually read operations) we have added the timeout exception to our custom error detection strategy, taken from the Transient Fault Handling Block; however as you stated this is not appropriate in most cases.

The best explanation we have received from Azure support thus far is that as SQL Azure is really a shared SQL Server instance that is used by multiple clients; if one user performs an intensive operation it can affect other users in this way. However; believe this not to be acceptable we are still in contact with SQL Azure support to ascertain why throttling is not stopping this sort of activity from affecting us.

You best bet is to:

  • Contact SQL Azure Support either through the forums or directly (If you have a support package)
  • If possible; try setting up a new SQL Azure instance and migrating your database across
    • Whilst we get this issue intermittently on one SQL Azure instance; we have never experienced it on our other 2 instances.

As a side note; we are still waiting on Azure Support to get back to us regarding why we were still receiving timeout exceptions.

Upvotes: 3

Related Questions