Large Table Times out - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Question

We have a customer who is experiencing Time-outs in our .NET application which uses SQL Server as the backend.

The table in question is the OrderLines table in a SQL Server Database that has 4155151 rows

Lately, the customer has reported, that intermittently they would get these Time-Outs during the process of updating this table.

The code doesn't have a specific Time-out set in the Connection String to the Database but on the SQL Server, the Time-out is set to 10 minutes.

The error reported is as follows:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

We have checked the Indexes for this table and it seems ok (see image attached).

The customers IT guys have checked processes/tasks running on the server throughout the day, and did mention they recently updated to .NET 4.6.1 . Also, they mentioned they have plenty of RAM allocated and disk latency is fine and CPU is fine as well.

Any ideas on how to trouble shoot this issue further?

Answer 1

The code doesn't have a specific Time-out set in the Connection String to the Database but on the SQL Server, the Time-out is set to 10 minutes.

You are most probably reaching a client timeout which by default is 30 seconds for C# SQL Server client (ADO.NET?). Consider increasing this timeout until you find a decent solution that does not force the client into waiting so much time.

We have checked the Indexes for this table and it seems ok (see image attached).

Typically indexes are not OK by default, but based on actual SELECT queries that hit the table. In your case you seem to issue an UPDATE and the first thing that comes into my mind is that you have a lot of indexes (5) on that table and UPDATE might need to also update most of them. Just from the name, idx_ordernumber and idx_ordernumber_Status seem to be very similar and they might be merged into a single index.

The following is mostly an approach made by a .NET programmer and it is not optimal from a DBA's perspective, but it might provide useful insight before diving into more DBA savvy stuff. Try this on a development / test database that has little activity, but a data volume similar to that from production:

1. Run SQL Server Profiler (yes, it is deprecated, but the tool is still very useful for quick checks)

2. Create a new trace and double check that you are filtering by your database, remove audit events and include events like *:Starting + *:Completed

3. Run your UPDATE scenario. You should see some queries and I expect that your problematic query to be more expensive (perhaps a rather large duration). Also, this step might reveal another issue: lots of queries issued by that "update" bundled into a single transaction that might take a very long time (in spite of each being very quick). This later is a client side issue, not a DB one.

4. You might run the query again in SQL Server Management Studio by also including Actual Execution Plan to see what is causing the rather long execution

If you still do not figure out what is the cause, edit your question and provide what you have found out: the query, the execution plan, duration etc.