Entity Framework "The wait operation timed out" on simple DeleteAll

Question

We have an MVC web application that I inherited that loads, let's call it MyCategory and all of its children into a webform, takes edit, then saves on save clicked. The way the application works is to first delete all data for the parent level entity in the model, then add everything back. We have pretty low concurrent user base on this, no more than twenty people saving at most every dew minutes each. We are unable to reproduce this error locally, but when the offshore team starts work we have started seeing the

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.]

error show up. This is happening when calling a delete from LINQ on the parent table (it has no data at this point, nor do child tables). It is intermittent, but has happened quite a few times in the past week, in which there was a ramp up in work on this portion of the project.

From the Stack Trace, it looks to be failing on System.Data.SqlClient.SqlCommand.FinishExecuteReader which appears to be going for 109+ minutes. This should be deleting at most tqo records from the table, and anyone loading data from this table should be retrieving at most two in a very short time span.

Any ideas on where to start would be appreciated. Unfortunately, I do not have permissions to run SQL Query Analyzer or Activity Monitor on the production database.

Call Stack is:

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +388
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +717
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4515
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6557561
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +6560327
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +586
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +742
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +287
   System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +789
   System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +188
   System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +500
   System.Data.Linq.StandardChangeDirector.DynamicDelete(TrackedObject item) +71
   System.Data.Linq.StandardChangeDirector.Delete(TrackedObject item) +258
   System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) +622
   System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +932
   WebAppData.MyCategory.MyCategoryData.DeleteAll(Int32 id, Guid gid) +1053
   WebAppServices.MyCategory.MyCategoryService.DeleteMyCategoryParentItems(Int32 id, Guid gId) +1632
   WebAppServices.MyCategory.MyCategoryService.UpdateMyCategory(Int32 id, Guid gId, MyCategoryEntity mce) +51
   WebAppUI.Areas.Documents.Categories.Sections.MyCategory.MyCategoryController.Save(Int32 Id, Guid gId, MyCategoryModel model) +93

EDIT:

Connection String:

<add name="Data" connectionString="Data Source=myserver;Initial Catalog=mydatabase;User ID=myuser;Password=mypassword />

Solution 1

This is one temporary solution for your issue right now.You can increase the Timeout as shown below.But you must find out the EF query which it causes this issue and need to improve the performance it.

Note : here time is seconds

public DbContext() : base("Default")
{
    this.Database.CommandTimeout = 60;
}

Solution 2

Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.

USE database_name;  
GO  
EXEC sp_updatestats;

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-updatestats-transact-sql?view=sql-server-2017

Comments

[Scuba Steve]

Silly me for thinking MS SQL would keep its indexes up to date!

[Gert Arnold]

That repeats this answer:stackoverflow.com/a/39399494/861716

[Aay Que]

@GertArnold I don't think so. There are chances that someone need to put time out in a specific query. :)

[Gert Arnold]

It's enough to know that CommandTimeout can be set. It's not necessary to spell out all possible use cases.

[AminRostami]

Well done , very nice