SQL Azure Timeout

2
votes

We are getting SQL Azure Timeout issues when trying to connect from a Hosted web role ASP.NET / Umbraco application). The issue is intermittant. The exception is:

At /microsoft-crm/microsoft-crm-2011.aspx (Referred by:xxxxxxxxxxx): umbraco.DataLayer.SqlHelperException: Umbraco Exception (DataLayer): SQL helper exception in ExecuteReader ---> 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.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.ReadByte() 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.ExecuteReader(CommandBehavior behavior) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at umbraco.DataLayer.SqlHelpers.SqlServer.SqlServerHelper.ExecuteReader(String commandText, SqlParameter[] parameters) at umbraco.DataLayer.SqlHelper1.ExecuteReader(String commandText, IParameter[] parameters) --- End of inner exception stack trace --- at umbraco.DataLayer.SqlHelper1.ExecuteReader(String commandText, IParameter[] parameters) at umbraco.macro..ctor(Int32 id) at umbraco.macro.ReturnFromAlias(String alias) at umbraco.presentation.templateControls.Macro.CreateChildControls()
at System.Web.UI.Control.EnsureChildControls() at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Our connection string to SQL Azure is:

Server=tcp:xxxxxxx.database.windows.net,1433;Database=DatabaseName;User ID=UserName@Servername;Password=Password;Trusted_Connection=False;Encrypt=True;TrustServerCertificate=True

2
asp.netsqlazureumbracoazure-sql-database
at umbraco.DataLayer.SqlHelper1.ExecuteReader(String commandText, IParameter[] parameters): can you debug and get actual commandText, then try to execute it in SSMS to see if it works there ? – astaykov

2 Answers

3
votes

The usual issues with debugging a SQL Server timeout apply, even though you're in the cloud.

The principal difference that I've found is that SQL Azure is generally slower than a dedicated on-premise server, both in terms of query speed and the speed of transmitting the data back to the Web application.

  • Remember indexes: you'll need them!
  • Turn on tracing and try to get hold of the actual query that's being executed. Evaluate the query plan (see http://www.sql-server-performance.com/2006/query-execution-plan-analysis/) and see if there's anything obvious that can be done with the table structure.
  • Make sure your SQL Azure instance is in the same data centre as your Web roles. Transferring lots of data is slow (and expensive!) between data centres.
  • Try to avoid patterns where lots of data is retrieved from the database, only to be filtered in the Web application. Rather, make sure the database does the "heavy lifting".

You may also find that if you are running lots of queries that your SQL Azure connection may become throttled. The reference to that is here: http://msdn.microsoft.com/en-us/library/windowsazure/ff394106.aspx#throttling -- however, as that gives a distinctly different error code I suspect that's not the situation here.

0
votes

We have been experiencing simular timeouts on SQL Azure and it turned out it was the session state db that was causing the timeouts to occur.

Once we switched to AppFabric cache for session state management we no longer experienced any timeouts.

More info here: http://our.umbraco.org/forum/core/general/27179-SQL-Azure-connectivity-issues