System.Data.SqlClient.SqlException: Timeout expired

Question

Coupled days ago I noticed that my web application giving me sql an exception timeout expired.

I cleaned up couple stored procedures taking more cpu and restarted SQL Server service and my application started work as it was before fast and without any delay. After a three four hours I checked it again and I could not load a page than it gave me the exception timeout expired. I checked server CPU is okay everything looks fine. I have some other website under the same IIS 7 and they run well without any exceptions. I restarted SQL Server service again and my application again back to normal. And I think it looks like the problem on SQL server database but I’m not sure how to troubleshoot it.

So each time when I’m getting exception I just restart sql service but of course it isn’t the best way. Please help me to resolve this issue.

Here is the one of the exception I got.

Message: Exception of type 'System.Web.HttpUnhandledException' was thrown. Source: System.Web Inner Exception:System.Data.UpdateException: An error occurred while updating the entries. See the InnerException for details. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary2 identifierValues, List1 generatedValues) at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) --- End of inner exception stack trace --- at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(Boolean acceptChangesDuringSave) at System.Data.Objects.ObjectContext.SaveChanges() at BCSCDomain.Domain.DataLayer.OtherDataLayer.UpdateHitCounter(Int32 hlistid, Int32 hcounterid) at BuyCarandSellCar.UsedCarProfilePage.HitCounter() at BuyCarandSellCar.UsedCarProfilePage.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.usedcarlistings_profilepage_aspx.ProcessRequest(HttpContext context) in c:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\79794658\835d6695\App_Web_kmrmpdbb.16.cs:line 0 at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Solution

Well, to start off, it's the function "UpdateHitCounter" that's proximally causing the problem, but you don't know whether this is a cause or an effect. Your query is exceeding the configured time allotted to complete.

Whenever you have a poorly performing database, a broad stroke approach you can run on it is to attach Sql Profiler to it, and then execute the Index Tuning Wizard against the results.

If you want to take a more measured, triage approach, you can restrict the profiler to log only queries that take longer than x seconds to complete, whatever you think x should be. I usually start out at 5 and work down from there if nothing shows up. Here is a primer on that topic.

Once you identify the long running queries, execute them in a local copy and examine the execution plans. Here is a primer for that, but to start out look for "table scan".

Ultimately, either your database is suboptimal, or your hardware isn't up to the traffic. It's almost certainly the first, and these two approaches should get you on your way.