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

Question

I am using Microsoft.EntityFrameworkCore.SqlServer 2.2.6. I have a stored procedure which typically takes 1 to 2 seconds to execute.

I am using .NET Core 2.2 with EF Core to execute that stored procedure.

appsettings.json:

{  
    "SqlCommandTimeout": 120, // seconds
    "ConnectionStrings": {
        "DefaultConnection": "Server=serverip;Database=MyDataBase;Integrated Security=True;"
    }
}

In startup.cs I am setting connection string and timeout

var sqlCommandTimeout = configuration.GetValue<int>("SqlCommandTimeout");
services.AddDbContext<MyDBContext>(options =>
        {
            options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"),
                sqlServerOptions => sqlServerOptions.CommandTimeout(sqlCommandTimeout));
        });

This code executes the stored procedure and populates a DTO:

public static async Task<IEnumerable<AvailableWorkDTO>> prcGetAvailableWork(this MyDBContext dbContext, int userID)
{            
    var timeout = dbContext.Database.GetCommandTimeout() ?? 120;

    var result = new List<AvailableWorkDTO>();

    using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
    {
        var p1 = new SqlParameter("@UserID", SqlDbType.Int)
            {
                Value = userID
            };

        cmd.CommandText = "dbo.prcGetAvailableWork";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(p1);
        cmd.CommandTimeout = timeout;

        await dbContext.Database.OpenConnectionAsync().ConfigureAwait(false);

        using (var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
        {
            while (await reader.ReadAsync().ConfigureAwait(false))
            {
                var item = new AvailableWorkDTO();
                item.ID = reader.GetInt32(0);
                item.Name = reader.GetString(1);
                item.Title = reader.GetString(2);
                item.Count = reader.GetInt32(3);

                result.Add(item);
            }
        }
    }

    return result;
}

The stored procedure only reads data from couple of tables using READUNCOMMITTED isolation level. However there is also a another background process that inserts new records into these tables every 3 minutes.

ISSUE

Every now and then when number of users increase we are seeing the timeout exception.

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.

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

System.ComponentModel.Win32Exception (258): The wait operation timed out
at System.Data.SqlClient.SqlCommand.<>c.b__122_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

--- End of stack trace from previous location where exception was thrown ---

at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)

--- End of stack trace from previous location where exception was thrown ---

at Data.Entities.StoredProcedures.StoredPrcedureExtensions.prcGetAvailableWork(MyDbContext dbContext, Int32 userID) in D:\Jenkins\xx-production\workspace\Src\Common\Data\Entities\StoredProcedures\StoredPrcedureExtensions.cs:line 56
at ....
....
Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)

at System.Threading.Tasks.ValueTask`1.get_Result()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)
at Microsoft.AspNetCore.Routing.EndpointRoutingMiddleware.Invoke(HttpContext httpContext)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)
ClientConnectionId:c10da510-509f-4bfb-8b37-58c0ee8fa3b1
Error Number:-2,State:0,Class:11at

Questions

1. I am using EF Core 2.2. Based on documentation, for certain async methods SqlCommand.CommandTimeout property is ignored. In the above code will the timeout ignored?

2. SQL Server Profiler shows stored procedure actually gets executed only once by await cmd.ExecuteReaderAsync() line. However we still need to keep connection open while reading from the reader. Is the CommandTimeOut includes time takes for reading from the reader?

3. At most 100 users may concurrently access this stored procedure. We have double check indexes and also execute scheduled indexing job every day. We are using

    Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) Standard Edition (64-bit)
   

   on Windows Server 2019. Which should be able to handle the load. Is there any settings that we need to look? (SQL Server also has multiple other databases as well)

Answer 1

In the above code will the timeout ignored?

No.

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.

This is a client-side timeout caused by the CommandTimeout.

Is the CommandTimeOut includes time takes for reading from the reader?

Yes. Per docs, SqlCommand.CommandTimeout includes SqlDataReader.Read() time.

Is there any settings that we need to look?

Turn on the Query Store and track the session wait stats to see if you're seeing blocking, CPU contention or something else.

Answer 2

I also had the same issue, in my case I written the stored procedure code in the following way

BEGIN TRAN
BEGIN TRY
  some code here....
 --COMMIT TRAN // it is missed or written in the wrong place
END TRY

"COMMIT TRAN" code missed in the stored procedure code but "BEGIN TRAN" is there, due to that previous calls are in waiting state and for the next transaction server will not respond