从 Web 调用时存储过程很慢，从 Management Studio 调用时很快 [英] Stored procedure slow when called from web, fast from Management Studio

问题描述

我的存储过程每次从 Web 应用程序调用时都会疯狂超时.

I have stored procedure that insanely times out every single time it's called from the web application.

我启动了 Sql Profiler 并跟踪了超时的调用，最后发现了这些事情:

I fired up the Sql Profiler and traced the calls that time out and finally found out these things:

1. 当从 MS SQL Management Studio 中执行语句时，使用相同的参数(实际上，我从 sql profile trace 复制了过程调用并运行了它):它平均在 5~6 秒内完成.
2. 但是当从 Web 应用程序调用时，它需要超过 30 秒(在跟踪中)所以我的网页实际上已经超时了.

除了我的 Web 应用程序有自己的用户这一事实之外，一切都相同(相同的数据库、连接、服务器等)我还尝试直接在工作室中与 Web 应用程序的用户一起运行查询，并且不会超过 6 秒.

Apart from the fact that my web application has its own user, every thing is same (same database, connection, server etc) I also tried running the query directly in the studio with the web application's user and it doesn't take more than 6 sec.

我如何知道发生了什么?

How do I find out what is happening?

我假设这与我们使用 BLL > DAL 层或表适配器的事实无关，因为跟踪清楚地显示延迟是在实际过程中.我能想到的就这些.

I am assuming it has nothing to do with the fact that we use BLL > DAL layers or Table adapters as the trace clearly shows the delay is in the actual procedure. That is all I can think of.

编辑 我在 此链接 ADO.NET 将 ARITHABORT 设置为 true - 这在大多数情况下都适用，但有时也适用发生这种情况，建议的解决方法是将 with recompile 选项添加到存储过程.就我而言，它不起作用，但我怀疑它与此非常相似.任何人都知道 ADO.NET 还能做什么或者我可以在哪里找到规范?

EDIT I found out in this link that ADO.NET sets ARITHABORT to true - which is good for most of the time but sometime this happens, and the suggested work-around is to add with recompile option to the stored proc. In my case, it's not working but I suspect it's something very similar to this. Anyone knows what else ADO.NET does or where I can find the spec?

推荐答案

我过去也遇到过类似的问题，所以我很想看到这个问题的解决方案.Aaron Bertrand 对 OP 的评论导致 从网络执行时查询超时，但从 SSMS 执行时速度超快，虽然问题不是重复的，但答案很可能适用于您的情况.

I've had a similar issue arise in the past, so I'm eager to see a resolution to this question. Aaron Bertrand's comment on the OP led to Query times out when executed from web, but super-fast when executed from SSMS, and while the question is not a duplicate, the answer may very well apply to your situation.

本质上，听起来 SQL Server 可能有一个损坏的缓存执行计划.您的网络服务器执行了错误的计划，但 SSMS 执行了不同的计划，因为 ARITHABORT 标志上有不同的设置(否则不会对您的特定查询/存储过程产生影响).

In essence, it sounds like SQL Server may have a corrupt cached execution plan. You're hitting the bad plan with your web server, but SSMS lands on a different plan since there is a different setting on the ARITHABORT flag (which would otherwise have no impact on your particular query/stored proc).

参见 ADO.NET 调用T-SQL 存储过程导致SqlTimeoutException 另一个例子，有更完整的解释和解决方案.

See ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException for another example, with a more complete explanation and resolution.

这篇关于从 Web 调用时存储过程很慢，从 Management Studio 调用时很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！