存储过程和查询计划不同 [英] Stored procedure and query plans different
问题描述
我有一个突然开始执行的存储过程。
查询计划看起来不对我，所以我复制/粘贴了代码并且
运行它（它是一个单独的SELECT语句）。运行得很好并使用
一个有意义的查询计划。现在，我知道你在想什么...
存储过程必须针对可变参数进行优化等。
这就是我所拥有的试图解决这个问题：
1.重新编译存储过程
2.创建一个新的但相同的存储过程
3.使用RECOMPILE选项创建存储过程
4.使用硬编码值创建存储过程而不是
aparameter
5 。改变存储过程使用动态SQL
在每种情况下，性能都没有提高，查询计划仍然相同（我不能轻易确认）这与动态SQL
版本相同，但性能仍然很糟糕。
我目前正在运行所有相关的UPDATE STATISTICS
表，但这需要一段时间。
有什么想法吗？
谢谢！
-Tom 。
我有一个突然开始执行的存储过程。
查询计划没有我看起来不对，所以我复制/粘贴代码并运行它（这是一个单独的SELECT语句）。运行得很顺利，并使用了一个有意义的查询计划。现在，我知道你们在想什么......
存储过程必须优化可变参数等。
这就是我试图解决的问题：
1.重新编译存储过程
2.创建一个新的但相同的存储过程
3.使用RECOMPILE选项创建存储过程
4.创建存储过程使用硬编码值代替
aparameter
5.更改存储过程以使用动态SQL
在每种情况下，性能都没有提高，查询计划仍然是
相同（我无法用动态SQL
版本轻松确认这一点，但性能仍然很糟糕）。
我猜这个查询取决于索引视图或索引的
计算列，并且您正在创建存储过程来自Enterprise
默认情况下关闭QUOTED_IDENTIFIER的管理器或OSQL。
此设置与存储一起保存程序，此设置必须打开，以便SQL Server在视图和计算列上使用索引。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
用于SQL Server SP3的联机丛书
http://www.microsoft.com/sql/techinf...2000/books.asp
极好的建议，但事实并非如此。 select语句只是来自表的
，其中没有一个使用计算列。
为了安全起见，我用QUOTED_IDENTIFIER重新创建了存储过程
on并且它对查询计划没有任何影响。我也尝试保存
它将ANSI NULLS设置为ON和OFF并且都没有效果。
昨晚我能够更新统计数据所有表格。
这也没有效果。目前我没有想法:(
谢谢，
-------------- ---
Thomas R. Hummel
高级数据库管理员
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参与在USENET ...获得奖励！
Thomas R. Hummel（to**************@hotmail.com）写道：优秀的建议，但情况并非如此。选择语句仅来自表格，其中没有一个使用计算列。
只是为了安全，我用QUOTED_IDENTIFIER
重新创建了存储过程，它对查询计划没有任何影响。我也尝试保存
它与ANSI NULLS设置为ON和OFF都没有效果。
昨晚我能够更新所有表格的统计数据。
这也没有效果。他那时我没有想法:(
没有看到代码或计划它很难说。但是
糟糕的计划怎么样？它涉及并行吗？在这种情况下，
选项（MAXDOP 1）可以解决这个问题。
-
Erland Sommarskog，SQL Server MVP ， es****@sommarskog.se
SQL Server SP3联机丛书在
http://www.microsoft .com / sql / techinf ... 2000 / books.asp
I have a stored procedure that suddenly started performing horribly.
The query plan didn''t look right to me, so I copy/pasted the code and
ran it (it''s a single SELECT statement). That ran pretty well and used
a query plan that made sense. Now, I know what you''re all thinking...
stored procedures have to optimize for variable parameters, etc.
Here''s what I''ve tried to fix the issue:
1. Recompiled the stored procedure
2. Created a new, but identical stored procedure
3. Created the stored procedure with the RECOMPILE option
4. Created the stored procedure with a hard-coded value instead of
aparameter
5. Changed the stored procedure to use dynamic SQL
In every case, performance did not improve and the query plan remained
the same (I could not easily confirm this with the dynamic SQL
version, but performance was still horrible).
I am currently running UPDATE STATISTICS on all of the involved
tables, but that will take awhile.
Any ideas?
Thanks!
-Tom.
Thomas R. Hummel (to********@hotmail.com) writes:I have a stored procedure that suddenly started performing horribly.
The query plan didn''t look right to me, so I copy/pasted the code and
ran it (it''s a single SELECT statement). That ran pretty well and used
a query plan that made sense. Now, I know what you''re all thinking...
stored procedures have to optimize for variable parameters, etc.
Here''s what I''ve tried to fix the issue:
1. Recompiled the stored procedure
2. Created a new, but identical stored procedure
3. Created the stored procedure with the RECOMPILE option
4. Created the stored procedure with a hard-coded value instead of
aparameter
5. Changed the stored procedure to use dynamic SQL
In every case, performance did not improve and the query plan remained
the same (I could not easily confirm this with the dynamic SQL
version, but performance was still horrible).
I would guess that the query depends on an indexed view or an indexed
computed columns, and you are creating stored procedures from Enterprise
Manager or OSQL that has QUOTED_IDENTIFIER off by default.
This setting is saved with the stored procedure, and this setting must
be on, for SQL Server to use indexes on views and computed columns.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Excellent suggestion, but that is not the case. The select statement is
from tables only, none of which use computed columns.
Just to be safe, I recreated the stored procedure with QUOTED_IDENTIFIER
on and it did not have any effect on the query plan. I also tried saving
it with ANSI NULLS set to both ON and OFF and neither had an effect.
Last night I was able to update the statistics on all of the tables.
That also had no effect. At the moment I am out of ideas :(
Thanks,
-----------------
Thomas R. Hummel
Senior Database Administrator
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
Thomas R. Hummel (to**************@hotmail.com) writes:Excellent suggestion, but that is not the case. The select statement is
from tables only, none of which use computed columns.
Just to be safe, I recreated the stored procedure with QUOTED_IDENTIFIER
on and it did not have any effect on the query plan. I also tried saving
it with ANSI NULLS set to both ON and OFF and neither had an effect.
Last night I was able to update the statistics on all of the tables.
That also had no effect. At the moment I am out of ideas :(
Not seeing the code or plans it''s difficult to say. But how does the
bad plan look like? Does it involve parallellism? In that case an
OPTION (MAXDOP 1) may do the trick.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于存储过程和查询计划不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
