过程在 ADO.NET 中超时，但在 SSMS 中没有 [英] Procedure times out from ADO.NET but not in SSMS

问题描述

我有一个存储过程，它给我一个 SqlException，因为当我从代码运行它时超时(超时设置为 30).
当我直接在 Management Studio 中运行该过程时，它会在 1 秒内执行.
我也只有在针对特定数据库运行时才会超时.
当我使用其他数据库时，它会很快完成而不会出错.
这是完整的错误信息:

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?

我尝试在进程运行时查看它，但它已暂停.sys.dm_os_waiting_tasks 将等待类型显示为 IO_COMPLETION(如果有任何用处).我使用 sp_who2 'active' 在数据库上看到的唯一进程是一个超时和我的活动 SSMS 窗口，所以我不认为这是一个阻塞问题.我验证了该数据库与工作数据库具有相同的索引，并且运行 dbcc checkdb 没有任何错误.如何确定超时原因?

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?

推荐答案

您可以将超时会话的 SET 选项与未超时会话的 SET 选项进行比较:

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>);

当您发现一些不同时，尝试在 SSMS 查询中将每个设置更改为相反的设置，直到您获得超时(或在发送查询之前手动设置应用代码中的选项).现在，我手边没有 2005 实例，所以没有测试这个查询.您可能需要注释掉一个或多个列名.

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.

这篇关于过程在 ADO.NET 中超时，但在 SSMS 中没有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！