存储过程和查询计划不同

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 andran it (it''s a single SELECT statement). That ran pretty well and useda 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 procedure2. Created a new, but identical stored procedure3. Created the stored procedure with the RECOMPILE option4. Created the stored procedure with a hard-coded value instead ofaparameter5. Changed the stored procedure to use dynamic SQL In every case, performance did not improve and the query plan remainedthe same (I could not easily confirm this with the dynamic SQLversion, but performance was still horrible). I am currently running UPDATE STATISTICS on all of the involvedtables, 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 indexedcomputed columns, and you are creating stored procedures from EnterpriseManager or OSQL that has QUOTED_IDENTIFIER off by default. This setting is saved with the stored procedure, and this setting mustbe 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.aspExcellent suggestion, but that is not the case. The select statement isfrom tables only, none of which use computed columns. Just to be safe, I recreated the stored procedure with QUOTED_IDENTIFIERon and it did not have any effect on the query plan. I also tried savingit 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. HummelSenior 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 thebad plan look like? Does it involve parallellism? In that case anOPTION (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 这篇关于存储过程和查询计划不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！