Procedure times out from ADO.NET but not in SSMS

Question

I've got a stored procedure that is giving me a SqlException because of a timeout when I run it from code (with timeout set to 30).
When I run the procedure directly in Management Studio, it executes in under 1 second.
I also only get the timeout when running it against a specific database.
When I use other databases it finishes quickly without an error.
Here is the full error message:

System.Data.SqlClient.SqlException (0x80131904): 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)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

Does this stack trace mean anything to anyone? It looks like maybe my sproc completed and it timed out while trying to read some kind of meta data?

I tried looking at the process while it is running and it is Suspended. sys.dm_os_waiting_tasks shows the wait type as IO_COMPLETION, if that's of any use. The only processes I see on the database using sp_who2 'active' are the one timing out and my active SSMS window so I don't think it's a blocking issue. I verified this database has the same indeces as a working database and ran dbcc checkdb without any errors on it. How can I determine the cause of the timeout?

Blowing

Are you logging into SSMS with the same credentials that your application is using? – Randarandal

possible duplicate of Query times out from web app but runs fine from management studio – Offend

probably. You can look at my anwer to a similar question here #5554467 – Gadoid

That is a good one too, however I'd hate to have to re-write all of my procedures to pass input parameter values to local variables. The OP's problem seemed to be a variance in the ARITHABORT setting but it really could be any of the SET options causing a different plan. – Offend

David, I'm using my windows account in SSMS but a SQL user from the application. Please see my reply to the answer below though. – Blowing

Answer 1

You can compare the SET options for the session that is timing out to those from the session that is not:

SELECT
    session_id,
    [ansi_defaults],
    [ansi_null_dflt_on],
    [ansi_nulls],
    [ansi_padding],
    [ansi_warnings],
    [arithabort],
    [concat_null_yields_null],
    [deadlock_priority],
    [quoted_identifier],
    [transaction_isolation_level]
FROM
    sys.dm_exec_sessions
WHERE
    session_id IN (<spid1>, <spid2>);

When you find some that are different, experiment with changing each setting to the opposite in your SSMS query until you get the timeout (or manually setting the option(s) in your app code before sending the query). Now, I don't have a 2005 instance handy, so have not tested this query. You may need to comment out one or more column names.

Offend

Thanks Aaron. The only difference was arithabort which was off from the app. I tried setting it to on but it didn't speed it up. I need to clarify a detail from my post above because I seem to have made a wrong assumption. In SSMS I was executing the SQL from the sproc by hardcoding its parameter. I discovered if I actually execute the stored procedure in SSMS and pass in the parameter, it takes 35-38 seconds to run which would time out. So why would running the SQL execute instantly but running it via a sproc execute 35 times slower? – Blowing

You'll need to check the execution plan. It's possible that the plan that was cached was not optimal either due to different stats at the time, atypical parameters, etc. Try adding WITH RECOMPILE to the stored procedure (or dropping it and re-creating it) to see if that has any impact on performance. – Offend

The execution plans did appear to differ slightly (I don't really know what I'm looking at). There was a nested loop in the efficient one where there was a hash match in the other and a few of the Cost percentages differed. So I dropped and recreated it. That made it execute quickly out of SSMS and run fast enough to not time out out of the application but it still took like 20-25 seconds in the application. While doing some more testing to see if arith abort was now the problem, I altered the sproc again and now it's back to taking over 30 seconds in SSMS and SQL. – Blowing

Answer 2

Dropping and re-creating the SP will clear the corrupted, cached Execution Plan

DROP PROCEDURE [dbo].[YourSPName]
GO
CREATE PROCEDURE [dbo].[YourSPName]
-- your SP Code

Incogitable