SQL Azure: Intermittent SQL timeouts

Question

We have a service (Azure Web Role) calling into SQL Azure via the Entity Framework. We get the following error intermittently. Load is ~10 req/s so that should not be the issue. Any idea on waht could be happening here?

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: 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)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()     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)     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)     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.ExecuteDbDataReader(CommandBehavior behavior)     at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)     --- End of inner exception stack trace ---     at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)     at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption)     at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)     at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)     ...

Answer 1

Hi,

This error probably means your code is experiencing a timeout due to a long running query. Although it may be possible that your code is receiving a timeout for other reasons, I would first investigate the performance of your query. The fact that some queries work and others don't does not mean it's not your code.

To investigate your queries and see how long they take see this article: http://msdn.microsoft.com/en-us/library/ff394114.aspx 

Keep in mind that if your queries take too long, they will timeout or get killed. This is a form of throttling. Take a look at the above link; there is a statement that shows how to access the execution plans of your statements. You should analyze the execution plans and see if you need to add indexes or change your statement so it runs faster.

 

---

Herve Roggero, MVP SQL Azure Co-Author: Pro SQL Azure http://www.herveroggero.com

Answer 2

Hi 

Get the query generated by Entity Framework and run it directly and see how much time it takes, long running queries will be throttled in SQL Azure

For information about getting queries generated in Entity Framework, Please see http://blog.cincura.net/227674-how-to-show-sql-command-created-by-entity-framework/

 

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"

Answer 3

I'm actually seeing the same thing.  Have been going back and forth with support for a few days now, but they haven't solved it yet, have just admitted that the problem appears to be on their end.  (My queries don't create enough load to cause timeouts.) Let me know if you reach a resolution.