I got an error Timeout expired.After,i run ALTER PROCEDURE command the error is gone.

Question

I have a problem about query timeout (store procedure called by .Net core application) this is an error.

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

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()   at System.Data.SqlClient.SqlDataReader.get_MetaData()   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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) 

This error occurs about once a week. The query run too slow and timeout but after I ALTER PROCEDURE without any changes or rebuild .Net core app the query speed in normal and error is fixed.

How can I fixed this problems ?

Answer 1

What you are describing is caused by "parameter sniffing". Running the alter proc statement removes the previous cached query plan, so the next execution generates a new better one.

https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Answer 2

How can I fixed this problems ?

How can we say, we don't know your database design, nor the code of your stored procedure.
Check the execution plan of the SP to see if suitable indexe(s) are used.
You can share the plan over https://www.brentozar.com/pastetheplan/

Answer 3

Hi @ballmill ,

It is a very clear message that the timeout duration specified for the operation is completed but actual work did not finish.
You said your error was a query timeout, because the default value for a web application or .NET application is generally 30 seconds, if any request is in progress and it couldn’t complete within the timeout period, this error will appear.
The reason a command/query runs longer than expected is commonly due to blocking or the need for query/index tuning or both.
A quick way to check for blocking to run sp_who2 while the query is running. The BlkBy column will show the SPID of the blocking connection if the query is blocked.
For a slow-running query, you may want to check the execution plan to verify that the statement is touching the rows which are needed. For example, if your intent is to Select a single row but you see a scan operator, that is a strong indication that you need to perform index or query tuning or need to update statistics.

You can change your SQLCommand .CommandTimeout:
As said before, by default command timeout is set to 30 seconds. Set it to 60, and your issue can probably be solved:

commandObject.Timeout = 60;  

https://stackoverflow.com/questions/6743320/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation