blocks|key|3144321|text|会不会是在web应用程序调用SP之前进行的其他DB调用保持事务处于打开状态？这可能是这个SP在web应用程序调用时等待的原因之一。我说隔离web应用程序中的调用(将其放到一个新页面上)，以确保web应用程序中的某些先前操作会导致此问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3144322|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Could it be that some other DB calls made before the web application calls the SP is keeping a transaction open? That could be a reason for this SP to wait when called by the web application. I say isolate the call in the web application (put it on a new page) to ensure that some prior action in the web application is causing this issue.

blocks|key|3144398|text|我以前也遇到过类似的问题，所以我很想看到这个问题的解决方案。亚伦·伯特兰对OP的评论导致了从web执行查询超时，但从SSMS执行时查询超时非常快。，虽然这个问题不是重复的，但答案很可能适用于你的情况。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3144399|本质上，听起来SQL+Server可能有一个损坏的缓存执行计划。您正在使用web服务器执行糟糕的计划，但是SSMS使用的是不同的计划，因为ARITHABORT标志上有不同的设置(否则不会对特定的查询/存储proc产生影响)。|3144400|另一个例子请参见ADO.NET调用T存储过程将导致SqlTimeoutException，并提供更完整的解释和解决方案。|3144401|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/2248112/sql-server-query-times-out-when-executed-from-web-but-super-fast-when-executed/|1|https://stackoverflow.com/questions/834124/ado-net-calling-t-sql-stored-procedure-causes-a-sqltimeoutexception/839055#839055^0|19|S|0|0|0|8|10|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]]]

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 <a href="https://stackoverflow.com/questions/2248112/sql-server-query-times-out-when-executed-from-web-but-super-fast-when-executed/">Query times out when executed from web, but super-fast when executed from SSMS</a>, and while the question is not a duplicate, the answer may very well apply to your situation.

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

See <a href="https://stackoverflow.com/questions/834124/ado-net-calling-t-sql-stored-procedure-causes-a-sqltimeoutexception/839055#839055">ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException</a> for another example, with a more complete explanation and resolution.

blocks|key|3144434|text|我还体验到，查询在web上运行得很慢，在SSMS中运行得很快，我最终发现问题是什么叫做参数嗅探。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3144435|我的修正是将sproc中使用的所有参数更改为局部变量。|3144436|例如：改变：|3144437|ALTER+PROCEDURE+[dbo].[sproc]+
++++@param1+int,
AS
SELECT+*+FROM+Table+WHERE+ID+=+@param1+|code-block|syntax|javascript|3144438|至：|3144439|ALTER+PROCEDURE+[dbo].[sproc]+
++++@param1+int,
AS
DECLARE+@param1a+int
SET+@param1a+=+@param1
SELECT+*+FROM+Table+WHERE+ID+=+@param1a|3144440|听起来很奇怪，但它解决了我的问题。|3144441|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I also experience that queries were running slowly from the web and fast in SSMS and I eventually found out that the problem was something called parameter sniffing. 

The fix for me was to change all the parameters that are used in the sproc to local variables.

eg. change:

<pre><code>ALTER PROCEDURE [dbo].[sproc] 
 @param1 int,
AS
SELECT * FROM Table WHERE ID = @param1 
</code></pre>

to:

<pre><code>ALTER PROCEDURE [dbo].[sproc] 
 @param1 int,
AS
DECLARE @param1a int
SET @param1a = @param1
SELECT * FROM Table WHERE ID = @param1a
</code></pre>

Seems strange, but it fixed my problem.

blocks|key|1590681|text|简单地重新编译存储过程(在我的例子中是表函数)为我工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1590682|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Simply recompiling the stored procedure (table function in my case) worked for me

blocks|key|2787793|text|就像赞恩说，这可能是由于参数嗅探。我经历了同样的行为，我查看了过程的执行计划和sp的所有语句(复制了过程中的所有语句，将参数声明为变量，并为变量签名了与参数相同的值)。然而，执行计划看起来完全不同。sp执行耗时3-4秒，并立即返回具有完全相同值的一行语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2787794|​|2787795|📷|atomic|offset|length|2787796|2787797|在谷歌搜索之后，我发现了一本关于这种行为的有趣读物：应用程序慢，在SSMS中快？|2787798|在编译过程时，Server不知道@fromdate的值是否发生变化，而是在@fromdate的值为NULL的假设下编译该过程。由于所有空值的比较结果未知，如果@fromdate在运行时仍然具有此值，则查询根本无法返回任何行。如果Server将输入值作为最终事实，那么它可以构造一个计划，只需一个不访问表的常量扫描(运行查询SELECT+*+FROM+Orders，其中OrderDate+>+NULL以查看此示例)。但是Server必须生成一个计划，该计划必须返回正确的结果，而不管运行时@time有什么值。另一方面，没有义务制定一项对所有价值观都最有利的计划。因此，由于假定不会返回任何行，所以Server会为Index进行设置。|blockquote|2787799|问题是，我有可以保留为空的参数，如果它们作为null传递，将用默认值初始化。|2787800|create+procedure+dbo.procedure
++++@dateTo+datetime+=+null
begin
++++if+(@dateTo+is+null)
++++begin
++++++++select+@dateTo++=+GETUTCDATE()
++++end

++++select+foo
++++from+dbo.table
++++where+createdDate+<+@dateTo
end|code-block|syntax|javascript|2787801|在我把它改成|2787802|create+procedure+dbo.procedure
++++@dateTo+datetime+=+null
begin
++++declare+@to+datetime+=+coalesce(@dateTo,+getutcdate())

++++select+foo
++++from+dbo.table
++++where+createdDate+<+@to
end+|2787803|它又像一种魅力。|2787804|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/5688d24337dc3245f22effaed67b1712.png|imageAlt|1|LINK|MUTABLE|url|http://www.sommarskog.se/query-plan-mysteries.html^0|0|0|0|1|0|0|0|Q|E|1|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1G|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1H|8|@]|9|@$G|1I|H|1J|1|1K]]|A|$]]|$1|I|3|C|5|6|7|1L|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1M|8|@]|9|@$G|1N|H|1O|1|1P]]|A|$]]|$1|L|3|M|5|N|7|1Q|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1R|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|1S|8|@]|9|@]|A|$T|U]]|$1|V|3|W|5|6|7|1T|8|@]|9|@]|A|$]]|$1|X|3|Y|5|S|7|1U|8|@]|9|@]|A|$T|U]]|$1|Z|3|10|5|6|7|1V|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18|19|-4]]|1A|$5|1B|15|1C|A|$1D|1E]]]]

like @Zane said it could be due to parameter sniffing. I experienced the same behaviour and I took a look at the execution plan of the procedure and all the statements of the sp in a row (copied all the statements form the procedure, declared the parameters as variables and asigned the same values for the variable as the parameters had). However the execution plan looked completely different. The sp execution took 3-4 seconds and the statements in a row with the exact same values was instantly returned.

<a href="https://i.stack.imgur.com/gjde1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gjde1.png" alt="executionplan"></a>

After some googling I found an interesting read about that behaviour: <a href="http://www.sommarskog.se/query-plan-mysteries.html" rel="nofollow noreferrer" title="Slow in the Application, Fast in SSMS?">Slow in the Application, Fast in SSMS?</a>

<blockquote>
 When compiling the procedure, SQL Server does not know that the value of @fromdate changes, but compiles the procedure under the assumption that @fromdate has the value NULL. Since all comparisons with NULL yield UNKNOWN, the query cannot return any rows at all, if @fromdate still has this value at run-time. If SQL Server would take the input value as the final truth, it could construct a plan with only a Constant Scan that does not access the table at all (run the query SELECT * FROM Orders WHERE OrderDate > NULL to see an example of this). But SQL Server must generate a plan which returns the correct result no matter what value @fromdate has at run-time. On the other hand, there is no obligation to build a plan which is the best for all values. Thus, since the assumption is that no rows will be returned, SQL Server settles for the Index Seek.
</blockquote>

The problem was that I had parameters which could be left null and if they were passed as null the would be initialised with a default value.

<pre><code>create procedure dbo.procedure
 @dateTo datetime = null
begin
 if (@dateTo is null)
 begin
 select @dateTo = GETUTCDATE()
 end

 select foo
 from dbo.table
 where createdDate &lt; @dateTo
end
</code></pre>

After I changed it to

<pre><code>create procedure dbo.procedure
 @dateTo datetime = null
begin
 declare @to datetime = coalesce(@dateTo, getutcdate())

 select foo
 from dbo.table
 where createdDate &lt; @to
end 
</code></pre>

it worked like a charm again.

blocks|key|2787846|text|不是垃圾邮件，而是希望对其他人有帮助的解决方案，我们的系统看到了高度超时。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2787847|我尝试使用sp_recompile设置要重新编译的存储过程，这解决了一个SP的问题。|offset|length|style|CODE|2787848|最终，有更多的SP是定时退出的，其中许多以前从未这样做过，通过使用DBCC+DROPCLEANBUFFERS和DBCC+FREEPROCCACHE，超时事件的发生率已经大幅下降--仍然存在一些孤立的事件，我怀疑计划的更新需要一段时间，还有一些SPs确实表现不佳，需要重新评估。|2787849|entityMap^0|0|5|C|0|X|L|1J|I|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|K|$]]

Not to spam, but as a hopefully helpful solution for others, our system saw a high degree of timeouts.
I tried setting the stored procedure to be recompiled by using <code>sp_recompile</code> and this resolved the issue for the one SP.
Ultimately there were a larger number of SP's that were timing-out, many of which had never done so before, by using <code>DBCC DROPCLEANBUFFERS</code> and <code>DBCC FREEPROCCACHE</code> the incident rate of timeouts has plummeted significantly - there are still isolated occurrences, some where I suspect the plan regeneration is taking a while, and some where the SPs are genuinely under-performant and need re-evaluation.

blocks|key|1590755|text|--BEFORE
CREATE+PROCEDURE+[dbo].[SP_DEMO]
(+
++++@ToUserId+bigint=null
+)
AS
BEGIN
SELECT+*+FROM+tbl_Logins+WHERE+LoginId+=+@ToUserId+
END
--AFTER+CHANGING+TO+IT+WORKING+FINE
CREATE+PROCEDURE+[dbo].[SP_DEMO]
(+
++++@ToUserId+bigint=null
+)
AS
BEGIN
DECLARE+@Toid+bigint=null
SET+@Toid=@ToUserId
SELECT+*+FROM+tbl_Logins+WHERE+LoginId+=+@Toid+
END|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1590756|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>--BEFORE
CREATE PROCEDURE [dbo].[SP_DEMO]
( 
 @ToUserId bigint=null
 )
AS
BEGIN
SELECT * FROM tbl_Logins WHERE LoginId = @ToUserId 
END
--AFTER CHANGING TO IT WORKING FINE
CREATE PROCEDURE [dbo].[SP_DEMO]
( 
 @ToUserId bigint=null
 )
AS
BEGIN
DECLARE @Toid bigint=null
SET @Toid=@ToUserId
SELECT * FROM tbl_Logins WHERE LoginId = @Toid 
END
</code></pre>

blocks|key|2787909|text|您可以通过以下方法针对特定的缓存执行计划：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2787910|SELECT+cp.plan_handle,+st.[text]
FROM+sys.dm_exec_cached_plans+AS+cp+
CROSS+APPLY+sys.dm_exec_sql_text(plan_handle)+AS+st
WHERE+[text]+LIKE+N'%25your+troublesome+SP+or+function+name+etc%25'|code-block|syntax|javascript|2787911|然后只删除引起问题的执行计划，例如：|2787912|DBCC+FREEPROCCACHE+(0x050006003FCA862F40A19A93010000000000000000000000)|2787913|我现在有一个任务，每5分钟运行一次，查找运行缓慢的过程或函数，并自动清除那些执行计划(如果发现的话)：|2787914|if+exists+(
++++SELECT+cpu_time,+*
++++FROM+sys.dm_exec_requests+req
++++CROSS+APPLY+sys.dm_exec_sql_text(sql_handle)+AS+sqltext
++++--order+by+req.total_elapsed_time+desc
++++WHERE+([text]+LIKE+N'%25your+troublesome+SP+or+function+name+etc%25')
++++and+cpu_time+>+8000
)
begin

++++SELECT+cp.plan_handle,+st.[text]
++++into+#results
++++FROM+sys.dm_exec_cached_plans+AS+cp+
++++CROSS+APPLY+sys.dm_exec_sql_text(plan_handle)+AS+st
++++WHERE+[text]+LIKE+N'%25your+troublesome+SP+or+function+name+etc%25'
++++delete+#results+where+text+like+'SELECT+cp.plan_handle%25'
++++--select+*+from+#results

++++declare+@handle+varbinary(max)
++++declare+@handleconverted+varchar(max)
++++declare+@sql+varchar(1000)

++++DECLARE+db_cursor+CURSOR+FOR++
++++select+plan_handle+from+#results

++++OPEN+db_cursor+++
++++FETCH+NEXT+FROM+db_cursor+INTO+@handle

++++WHILE+@@FETCH_STATUS+=+0+++
++++BEGIN+++

++++++++--e.g.+DBCC+FREEPROCCACHE+(0x050006003FCA862F40A19A93010000000000000000000000)
++++++++print+@handle
++++++++set+@handleconverted+=+'0x'+%2B+CAST(''+AS+XML).value('xs:hexBinary(sql:variable("@handle"))',+'VARCHAR(MAX)')
++++++++print+@handleconverted
++++++++set+@sql+=+'DBCC+FREEPROCCACHE+('+%2B+@handleconverted+%2B+')'
++++++++print+'DELETING:+'+%2B+@sql
++++++++EXEC(@sql)

++++++++FETCH+NEXT+FROM+db_cursor+INTO+@handle
++++END+++

++++CLOSE+db_cursor+++
++++DEALLOCATE+db_cursor

++++drop+table+#results

end|2787915|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You can target specific cached execution plans via:
<pre><code>SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%your troublesome SP or function name etc%'
</code></pre>
And then remove only the execution plans causing issues via, for example:
<pre><code>DBCC FREEPROCCACHE (0x050006003FCA862F40A19A93010000000000000000000000)
</code></pre>
I've now got a job running every 5 minutes that looks for slow running procedures or functions and automatically clears down those execution plans if it finds any:
<pre><code>if exists (
 SELECT cpu_time, *
 FROM sys.dm_exec_requests req
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
 --order by req.total_elapsed_time desc
 WHERE ([text] LIKE N'%your troublesome SP or function name etc%')
 and cpu_time &gt; 8000
)
begin

 SELECT cp.plan_handle, st.[text]
 into #results
 FROM sys.dm_exec_cached_plans AS cp 
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
 WHERE [text] LIKE N'%your troublesome SP or function name etc%'
 delete #results where text like 'SELECT cp.plan_handle%'
 --select * from #results

 declare @handle varbinary(max)
 declare @handleconverted varchar(max)
 declare @sql varchar(1000)

 DECLARE db_cursor CURSOR FOR 
 select plan_handle from #results

 OPEN db_cursor 
 FETCH NEXT FROM db_cursor INTO @handle

 WHILE @@FETCH_STATUS = 0 
 BEGIN 

 --e.g. DBCC FREEPROCCACHE (0x050006003FCA862F40A19A93010000000000000000000000)
 print @handle
 set @handleconverted = '0x' + CAST('' AS XML).value('xs:hexBinary(sql:variable(&quot;@handle&quot;))', 'VARCHAR(MAX)')
 print @handleconverted
 set @sql = 'DBCC FREEPROCCACHE (' + @handleconverted + ')'
 print 'DELETING: ' + @sql
 EXEC(@sql)

 FETCH NEXT FROM db_cursor INTO @handle
 END 

 CLOSE db_cursor 
 DEALLOCATE db_cursor

 drop table #results

end
</code></pre>

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

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

<ol>
<li>When executed the statements from within the MS SQL Management Studio, with same arguments (in fact, I copied the procedure call from sql profile trace and ran it): It finishes in 5~6 seconds avg. </li>
<li>But when called from web application, it takes in excess of 30 seconds (in trace) so my webpage actually times out by then. </li>
</ol>

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? 

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.

EDIT I found out in <a href="http://aspadvice.com/blogs/ssmith/archive/2008/02/15/Stored-Procedure-Performance-Varies-Between-ADO.NET-and-Management-Studio.aspx" rel="noreferrer">this link</a> that ADO.NET sets <code>ARITHABORT</code> to true - which is good for most of the time but sometime this happens, and the suggested work-around is to add <code>with recompile</code> 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?

Stored procedure slow when called from web, fast from Management Studio

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有存储过程，每次从web应用程序调用它时，都会疯狂地超时。我启动了并跟踪了那个时间的调用，最后发现了以下情况：在中执行语句时，参数相同(实际上，我从SQL跟踪复制了过程调用并运行它)：它在5~6秒内完成。但是当从web应用程序调用时，它需要超过30秒(在跟踪中)，所以我的网页实际上在那时超时了。除了我的web应用程序...

问从web调用时存储过程速度较慢，从Management调用时速度较快
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从web调用时存储过程速度较慢，从Management调用时速度较快EN