同一个存储过程的不同执行计划 [英] Different Execution Plan for the same Stored Procedure

问题描述

我们有一个查询在我们的生产系统上需要大约 5 秒，但在我们的镜像系统(尽可能与生产相同)和开发系统上需要不到 1 秒.

We have a query that is taking around 5 sec on our production system, but on our mirror system (as identical as possible to production) and dev systems it takes under 1 second.

我们已经检查了查询计划，我们可以看到它们有所不同.同样从这些计划中我们可以看出为什么一个比另一个花费更长的时间.数据、方案和服务器相似，存储过程相同.

We have checked out the query plans and we can see that they differ. Also from these plans we can see why one is taking longer than the other. The data, schame and servers are similar and the stored procedures identical.

我们知道如何通过重新排列连接和添加提示来修复它，但是目前如果我们不必对 SProc (Paperwork) 进行任何更改会更容易.我们也试过 sp_recompile.

We know how to fix it by re-arranging the joins and adding hints, However at the moment it would be easier if we didn't have to make any changes to the SProc (Paperwork). We have also tried a sp_recompile.

什么可能导致两个查询计划之间的差异?

What could cause the difference between the two query plans?

系统:SQL 2005 SP2 Enterprise on Win2k3 Enterprise

System: SQL 2005 SP2 Enterprise on Win2k3 Enterprise

更新:感谢您的回复，原来是统计数据.请参阅下面的摘要.

Update: Thanks for your responses, it turns out that it was statistics. See summary below.

推荐答案

您的统计数据很可能已过时.如果您的数据相同，请重新计算两台服务器上的统计信息并重新编译.然后您应该会看到相同的查询计划.

Your statistics are most likely out of date. If your data is the same, recompute the statistics on both servers and recompile. You should then see identical query plans.

此外，请仔细检查您的索引是否相同.

Also, double-check that your indexes are identical.

这篇关于同一个存储过程的不同执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！