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

Question

I am using asp.net core 3.1, ADO.NET , Azure SQL Database in this implementation. In this case an api call is made to the service layer -> database layer using stored procedure.

Azure SQL Pricing specification:

GeneralPurpose: Gen5, 8 vCores

Here goes the code :

public dynamic GetData(FiltersDto reportFilters, long typeid)
{
    List<DashboardDTO> result = new List<DashboardDTO>();
    DataSet ds = new DataSet();
    using (var connection = new SqlConnection(_objDBContext.Database.GetDbConnection().ConnectionString))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "[TT].[LoadDashboard]";
            command.Parameters.Add(new SqlParameter("@ReviewYear", SqlDbType.BigInt)
            {Value = reportFilters.ReviewYear});
            command.Parameters.Add(new SqlParameter("@Region", SqlDbType.Structured)
            {Value = GetTable<int>(reportFilters.Region)});
            command.Parameters.Add(new SqlParameter("@TypeId", typeid));
            using (var sda = new SqlDataAdapter())
            {
                sda.SelectCommand = command;
                sda.Fill(ds);
            }
        }

        connection.Close();
    }

    if (ds.Tables.Count > 0)
    {
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            // Logic to read and set the data
            DashboardDTO dashboard = new dashboardDTO
            {
              // Fill the dto with data
            }result.Add(dashboard);
        }
    }

    return result;
}

I am calling the above method from an asp.net core web api and receiving the below error:

{
    "header": "An unhandled error occurred.",
    "type": "SqlException",
    "errorcode": 10002,
    "message": "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.",
    "innerExcepioin": "The wait operation timed out.",
    "stackTrace": "   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)\r\n"
}

I also find that it took 31.37s on average to get the above error.

Can anyone help me with their guidance to fix this issue

Answer 1

You are hitting the default command timeout of 30s.

The stored procedure you are running is taking longer than that to execute. So the driver gives up its execution.

You can set your own command timeout: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=dotnet-plat-ext-6.0

command.CommandTimeout = 60; // For 60 seconds for example

You can also check the stored procedure to see if you can speed it up.