SQL Timeout expired for 2 second query

Question

The problem is I have a stored procedure that runs consistently in Sql Server Management Server with a time of 2 seconds but when calling that same stored procedure from code it times out.

When it runs correctly from SSMS it should return about 30 rows.

I've tried a few different ways to call the procedure from code but every time the same result. This just recently started happening, yesterday it was working fine.

The preferred method for calling the procedure for us is using Linq2Sql which gives the following error message:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

Line 16:         public IMultipleResults GetTournamentRatingNonComplaintData([global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "DateTime")] System.Nullable<System.DateTime> startdate, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "DateTime")] System.Nullable<System.DateTime> enddate, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "Int")] System.Nullable<int> officialSportID, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "Char(1)")] System.Nullable<char> gender, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType = "Int")] System.Nullable<int> level)
Line 17:         {
Line 18:             IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), startdate, enddate, officialSportID, gender, level);
Line 19:             return ((IMultipleResults)(result.ReturnValue));
Line 20:         }

Does anyone know what the differences are between running it in SSMS and through code? What can be done to troubleshoot this issue?

Answer 1

Your connection from SQL Management studio may be configured differently from the connection string you're using from .NET.

For example, see the options that are set by default in SQL Management Studio Tools/Options/Query Execution/SQL Server/Advanced.

I would suspect some setting that is different between the two environments is causing your problem. To debug you should try to configure SQL Server Management Studio to use the same configuration as your .NET connection string before running your SP.

Or alternatively, add SET statements to your .NET query to configure all the options you see configured in Management Studio.

It's not always easy to find the setting that's different - in my experience a common cause is different ANSI settings.