ADO.NET调用T-SQL存储过程导致SqlTimeoutException [英] ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException

问题描述

我有一个T-SQL存储过程签名

 创建过程MyProc
@recordCount INT输出
@参数1 INT
...
 

当直接在SQL Server上执行的程序运行下5秒，返回几个结果集数约100行的总和。

调用使用ADO.NET SqlDataAdapter.Fill 方法来填充一个数据集此过程引起 SqlTimeoutException 在的SqlCommand 3分钟（在指定的超时时间）之后。

更改存储的过程，以便它不再具有一个输出参数，以及需要被返回作为最后结果集的输出值，解决了问题，并在整个事情下按预期5秒运行在

但是，为什么？

我不想通过我的code碱基，并修改此类行为的所有实例不理解，如果我真的解决了这个问题。

另外需要注意的是，这是唯一明显的一个特定的服务器，这固然有更大的数据集比我们运行其它类似的数据库上。当然不是SQL Server设置？

更新

走进出现的问题是在元数据检索框架来源。在 SqlDataReader的对象无限期挂起的 ConsumeMetaData 方法。不过我跑在其他数据库测试和无法重现，所以当这个过程被称为虽然ADO.NET数据库具体问题...大。

更新II 已经证实，如果我改变code使用 OleDbDataAdapter的与SQLOLEDB orSQLNCLI提供者类型的问题仍然存在。肯定做连接。

解决方案

在我确定这是问题的根源ADO.NET连接，这样的发使我的答案。

默认情况下，通过SQL Server Management Studio中（SSMS）基本连接设置 ARITHABORT ON 。 ADO.NET连接没有。

设置 ARITHABORT OFF 并直接通过SSMS执行查询给了我同样的反应时间慢。

有或没有此设置运行时，主要的差别是一个不同的查询计划用于两个呼叫建立。当 ARITHABORT 是关闭，SSMS的命令将使用pre-编译缓存的查询计划的ADO。 NET连接使用，因此暂停。

通过在数据库上运行下面的命令以管理员身份运行的所有查询都按预期无论 ARITHABORT 设置。

  DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
 

我只能假设一个编译的查询计划成为腐败或无效的。

我会用这个去作为解决方案（我已经向上投票的答案）其他线程

感谢。

I have a T-SQL stored procedure with the signature

CREATE PROCEDURE MyProc
@recordCount INT OUTPUT
@param1 INT
...

When executed directly in Sql Server the procedure runs in under 5 seconds, returning a few result sets amounting to about 100 rows in total.

Calling this procedure using the ADO.NET SqlDataAdapter.Fill method to populate a Dataset causes a SqlTimeoutException on the SqlCommand after 3 minutes (the specified timeout interval).

Changing the stored procedure so that it no longer has an output parameter, and that the output value required is returned as the last result set, solves the problem, and the whole thing runs in under 5 seconds as expected.

But why?

I don't want to go through my code base and modify all instances of this type of behaviour without understanding if I have really solved the problem.

Another thing to note is this is only apparent on one particular server, which admittedly has a larger dataset than other similar databases we run. Surely not a Sql Server setting?

UPDATE

Stepping into the framework source the issue appears to be in metadata retrieval. The ConsumeMetaData method of the SqlDataReader object hangs indefinitely. However I ran tests on other databases and cannot reproduce, so it is a database specific issue when this procedure is called though ADO.NET...Great.

Update II Have confirmed the issue still occurs if I change the code to use the OleDbDataAdapter with the SQLOLEDB orSQLNCLI provider types. Definitely to do with the connection.

解决方案

Once I determined that it is the ADO.NET connection at the root of the problem, this thread led me to the answer.

Basically connections through Sql Server Management Studio (SSMS) by default have SET ARITHABORT ON. ADO.NET connections do not.

Setting ARITHABORT OFF and executing the query directly through SSMS gives me the same slow response time.

The main difference when running with or without this setting is a different query plan is created for the two calls. When ARITHABORT was OFF, the SSMS command would use the pre-compiled cached query plan that the ADO.NET connection was using, and therefore timeout.

By running the following commands as administrator on the database all queries run as expected regardless of the ARITHABORT setting.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

I can only assume a compiled query plan became corrupt, or invalid.

I will go with this as the solution (I have up-voted the answer) on the other thread

Thanks.

这篇关于ADO.NET调用T-SQL存储过程导致SqlTimeoutException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！