存储过程从Management Studio中减缓从Web调用，快速 [英] 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事件探查器和跟踪那个时候叫一声，终于找到了这些东西：

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轮廓跟踪过程调用和运行它）：它结束在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 doesnt take more than 6 sec.

所以这是非常令人困惑，如何才能知道发生了什么事？

So it's very confusing, 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.

谢谢了。

修改我在<一个发现href=\"http://aspadvice.com/blogs/ssmith/archive/2008/02/15/Stored-Procedure-Performance-Varies-Between-ADO.NET-and-Management-Studio.aspx\">this链接的ADO.NET套 ARITHABORT 来真的 - 这是很好的大部分时间，但有时出现这种情况，并建议解决方法是添加与重新编译选项的存储过程。在我而言，它不工作，但我怀疑这是非常类似的东西了这一点。任何人都知道还有什么不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? thanks.

推荐答案

我已经在过去出现类似的问题，所以我渴望看到一个解决这个问题。阿龙贝特朗的OP上的评论导致<一个href=\"http://stackoverflow.com/questions/2248112/sql-server-query-times-out-when-executed-from-web-but-super-fast-when-executed/\">SQL服务器：查询超时时，从网络运行，但超高速从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 SQL Server: 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可能有损坏高速缓存的执行计划。你打的不好的计划，您的Web服务器，但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.

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