SqlServer Timeout in Azure

bug?
1

I have Hangfire 1.3 running in Azure Cloud Service with an Azure SqlServer database. Everything is working fine except when I access the dashboard. When I have a huge load of scheduled job (~30k), I receive that error 1 request out of 2 :

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.QueryMultipleImpl(IDbConnection cnn, CommandDefinition& command)
   at Hangfire.SqlServer.SqlServerMonitoringApi.<GetStatistics>b__50(SqlConnection connection)
   at Hangfire.SqlServer.SqlServerMonitoringApi.UseConnection[T](Func`2 action)
   at Hangfire.Dashboard.Pages.LayoutPage.Execute()
   at Hangfire.Dashboard.RazorPage.TransformText(String innerContent)
   at Hangfire.Dashboard.RazorPageDispatcher.Dispatch(RequestDispatcherContext context)

Any idea?

2

(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)

3

There are two solutions for this problem:

  1. Increase the timeout value. But it will work only if it is too low so far. What timeout value for SQL operations do you have?
  2. Investigate what causes the timeout. The monitoring API uses read uncommitted transaction isolation level to avoid blocking. Does SQL Azure have this isolation level?
4

I’ve looked into the SqlServerConnection class and it looks like the CommandTimeout is not set before executing a request. When this option is not set, C# will kill the command after 30 seconds which explain my problem. It could be an improvment to configure the CommandTimeout as a configuration to make sure that a client with a lot of job will work fine.

http://grab.by/EdmK

I’ve been trying to increase the performance of my setup in Azure and it seems that the new SQL Azure (Basic, Standard and Premium editions) is not working fine due to performance. I switched back my database to a Web and Business Edition and everything is ok.

My tips for now would be to use Hangfire with SQL Azure Web & Business edition (or with an on-premise SQL edition) since the performance of the new service tier is not good enough.

5

SQL operation that takes longer that 30 seconds is very strange, and I’m sure that increasing the timeout will work fine for this case. I’ve read that read uncommitted isolation level exists on SQL Azure, so it seems to me that timeouts are caused by overloaded database instance.

This timeouts may be also caused by old expiration manager, if you have many expired records (fixed in 1.3.2). But it may also be caused by the main application itself.

Thank you for the tips on SQL Azure!