Linq Stored Procedure Timeout but SSMS Quick

Question

I have a stored procedure which I am calling using LinqToSQL. I'm not doing anything special at all, e.g.

MyDataContext db = new MyDataContext()

var results = db.storedProcedure(param1, param2, param3)

// Do stuff

When I run the stored procedure using the exact same parameters I get results between 2 and 6 seconds. The database is a remote database.

However, when I run the stored procedure it takes (after debugging....) 275 seconds! Under normal circumstances this gives the following exception:

[Win32Exception (0x80004005): The wait operation timed out]

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +1753346 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5295154 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +59 System.Data.SqlClient.SqlDataReader.get_MetaData() +90 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.ExecuteReader() +12 System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +1306 System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +118 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +342 System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) +83

All other stored procedures are called in the same way, but none have this issue. The remote DB Admin says he can see the call start and finish before the timeout occurs, so it seems to be something to do with the steps AFTER Linq receives the data.

Has anyone experienced this before and any ideas how to fix it?

I have tried removing the SP from the dmbl file and re-adding it. It noticed a change in one of the values from decimal to double, but apart from that its all the same.

As always, it was working fine yesterday!

Thanks in advance.

Answer 1

Ok, I finally discovered the REAL answer to this problem. SSMS typically uses an ARITHABORT ON and code typically uses ARITHABORT OFF - this is basically an option for how to handle what happens if a mathematical line in the code has an error - e.g. divide by zero.

The main thing here, though, is that both methods have a different execution plan - which is why the same thing can (randomly) take a lot longer on the website than in SSMS.

The execution plans are compiled based on estimates of the first time it is used so what you randomly find is that the execution plan is cached in a terrible way that suits your first query but is horrible for subsequent queries. This is what happened here and it is also why it just suddenly started working again - a new query plan was created after the stored procedure was changed.

In the end we used WITH RECOMPILE in the stored procedure - so there is no efficient re-use of the execution plan, but we didn't notice any difference anyway and the problem has not occurred since.