本文介绍了从 Web 调用时存储过程很慢,从 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 调用时很快的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 18:58
查看更多