ADO。NET调用T-SQL存储过程会导致SqlTimeoutException

I have a T-SQL stored procedure with the signature

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

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.

当直接在Sql Server中执行时，该过程在5秒内运行，返回一些结果集，总共大约100行。

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

使用ADO调用此过程。净SqlDataAdapter。填充方法填充数据集，在3分钟(指定的超时间隔)后，在SqlCommand中导致SqlTimeoutException。

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.

更改存储过程，使其不再具有输出参数，并将所需的输出值作为最后的结果集返回，从而解决了问题，整个过程按照预期在5秒内运行。

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?

需要注意的另一件事是，这只在一个特定的服务器上显示出来，它的数据集显然比我们运行的其他类似数据库大。肯定不是Sql服务器设置吗?

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.

进入框架源代码，问题似乎是在元数据检索中。SqlDataReader对象的ConsumeMetaData方法无限期挂起。但是，我在其他数据库上运行了测试，并且无法复制，所以在通过ADO.NET调用这个过程时，这是一个数据库特定的问题。太好了。

UPDATE II

更新二世

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

如果我将代码更改为使用具有SQLOLEDB或SQLNCLI提供程序类型的OleDbDataAdapter，则仍然会发生问题。这与连接有关。

3 个解决方案

#1

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.

基本上，通过Sql Server Management Studio (SSMS)的连接默认设置了算术运算。ADO。网络连接不。

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

设置算数abort并通过SSMS直接执行查询，得到了相同的慢响应时间。

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.

使用或不使用此设置运行时的主要区别是为这两个调用创建了不同的查询计划。当计算结束时，SSMS命令将使用ADO的预编译的缓存查询计划。正在使用NET连接，因此超时。

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.

谢谢。

#2

I stand corrected - yes, you CAN have both - an OUTPUT parameter as well as a set of rows being returned. You learn something new every day :-)

我更正一下——是的，您可以同时拥有一个输出参数和一组正在返回的行。你每天都在学习新的东西:-)

As to why a timeout happens - hmm.... hard to tell. A quickie little sample works fine for me. Can you post your stored proc (at least relevant bits of it)?

为什么会发生超时——嗯....很难说。一个快速的小样本对我很有效。你能上传你的存储proc(至少是相关的部分)吗?

How many rows are we talking about, that get returned here?

这里返回多少行?

At what point in your stored proc are you calculating the number of rows that you need to return back as OUTPUT parameter?

在存储的proc中，您是在计算需要作为输出参数返回的行数吗?

What if you try to add another parameter MaxRows to your one SProc as a test and do a SELECT TOP (@MaxRows)....... on your data? Does that return quickly?

如果您尝试将另一个参数MaxRows添加到您的一个SProc作为测试，并做一个SELECT TOP (@MaxRows)……在你的数据?很快恢复吗?

Marc

马克

#3

In short - I fixed my issue by forcing SQL Server to use the most appropriate index to limit lob logical reads when it couldn't figure it out on its own.

简而言之，我通过强制SQL Server使用最合适的索引来限制lob逻辑读取来解决我的问题。

In long -

在长-

I just ran into this issue and resolved it in a different way after trying all of the other suggested answers. In SSMS the query was running in ~3s, but was timing out when called from a .Net MVC web application.

我只是遇到了这个问题，在尝试了所有其他的建议答案后，以一种不同的方式解决了这个问题。在SSMS中，查询是在~3s中运行的，但是当从。net MVC web应用程序中调用时，该查询就超时了。

Statistics IO output in SSMS was telling me that there were over 195,500,000 lob logical reads on one table (20M-row table with a clustered columnstore index and also has row indexes, but has no "LOB" columns). I noticed from the execution plan that a bulk of the load (76%) was coming from an index seek on one of the row indices. I used the following:

SSMS中的统计IO输出告诉我，一个表上有超过195,500,000个lob逻辑读取(20m行表具有集群的columnstore索引，并且还有行索引，但是没有“lob”列)。我从执行计划中注意到，大部分负载(76%)来自一行索引的查找。我用以下:

from [table] with (index([clustered columnstore index name]))

in my query to force the usage of the clustered columnstore index and my query was reduced to <1s and the lob logical reads dropped to <6k from >195M, and when calling the SP from the web app now, it is round-tripping in 1.3s.

在我的查询中，为了强制使用群集的columnstore索引，我的查询被减少到<1，而lob逻辑读取从>195M下降到<6k，现在在web应用程序中调用SP时，它的循环周期是1.3秒。

I tried option recompile, set arithabort on, parameter sniffing, and in the end SQL Server just couldn't figure out which index to use. This is an edge case too btw, and the only time I have had to force an index in this database.

我尝试了选项重新编译，设置算数abort，参数嗅探，最后SQL Server无法确定使用哪个索引。顺便说一句，这也是一种边缘情况，也是我唯一一次在这个数据库中强制使用索引。

#1