Sql Server 2000 存储过程防止并行或其他什么? [英] Sql Server 2000 Stored Procedure Prevent Parallelism or something?

问题描述

我有一个巨大的令人作呕的存储过程，几个月前它并不慢，但现在是.我几乎不知道这个东西是做什么的，我也没有兴趣重写它.

I have a huge disgusting stored procedure that wasn't slow a couple months ago, but now is. I barely know what this thing does and I am in no way interested in rewriting it.

我知道，如果我获取存储过程的主体，然后声明/设置参数的值并在查询分析器中运行它，它的运行速度会提高 20 倍以上.

I do know that if I take the body of the stored procedure and then declare/set the values of the parameters and run it in query analyzer that it runs more than 20x faster.

从互联网上，我读到这可能是由于缓存的查询计划错误.因此，我尝试在 EXEC 之后使用WITH RECOMPILE"运行 sp，我也尝试将WITH RECOMPLE"放入 sp 中，但这些都没有帮助.

From the internet, I've read that this is probably due to a bad cached query plan. So, I've tried running the sp with "WITH RECOMPILE" after the EXEC and I've also tried putting the "WITH RECOMPLE" inside the sp, but neither of those helped even a little bit.

当我查看 sp 与查询的执行计划时，最大的区别是 sp 到处都有并行"操作，而查询没有任何操作.这可能是速度差异的原因吗?

When I look at the execution plan of the sp vs the query, the biggest difference is that the sp has "Parallelism" operations all over the place and the query doesn't have any. Can this be the cause of the difference in speeds?

谢谢，任何想法都会很棒...我被困住了.

Thank you, any ideas would be great... I'm stuck.

推荐答案

如果两个查询计划的唯一区别是并行性，请尝试将 OPTION (MAXDOP 1) 放在查询的末尾以将其限制为串行计划.

If the only difference between the two query plans is parallelism, try putting OPTION (MAXDOP 1) at the end of the query to limit it to a serial plan.

至于它们为何不同，我不确定，但我记得 SQL Server 2000 优化器是，嗯，挑剔.与您的情况类似，我们通常看到的是临时查询批处理会很快，而通过 sp_executesql 进行的相同查询会很慢.从来没有完全弄清楚发生了什么.

As to why they are different, I'm not sure, but I remember the SQL Server 2000 optimizer as being, um, finicky. Similar to your case, what we usually saw was that ad-hoc query batches would be fast and the same query via sp_executesql would be slow. Never did fully figure out what was going on.

不过，串行与并行绝对可以解释速度的差异.在 SQL Server 2000 上，并行计划使用所有机器上的处理器，不仅仅是它需要的那些:

Serial v parallel can definitely explain the difference in speeds, though. On SQL Server 2000, parallel plans use all the processors on the machine, not just the ones it needs:

如果 SQL Server 选择使用并行性，它必须使用所有配置的处理器(由 MAXDOP 查询提示配置确定)来执行并行计划.例如，如果您在 32 路服务器上使用 MAXDOP=0，则 SQL Server 会尝试使用所有 32 个处理器，即使与仅使用一个处理器的串行计划相比，七个处理器可能更有效地执行作业.由于这种全有或全无的行为，如果 SQL Server 选择并行计划并且您不限制 MAXDOP 查询提示[...]，则 SQL Server 协调高端服务器上的所有处理器所需的时间超过了使用并行计划的优势.

If SQL Server chooses to use parallelism, it must use all the configured processors (as determined by the MAXDOP query hint configuration) for the execution of a parallel plan. For example, if you use MAXDOP=0 on a 32-way server, SQL Server tries to use all 32 processors even if seven processors might perform the job more efficiently as compared to a serial plan that only uses one processor. Because of this all-or-nothing behavior, if SQL Server chooses the parallel plan and you do not restrict the MAXDOP query hint[...], the time that it takes SQL Server to coordinate all the processors on a high-end server outweighs the advantages of using a parallel plan.

默认情况下，我认为 MAXDOP 的服务器范围设置是 0，这意味着尽可能多地使用.如果您最近使用更多处理器升级了数据库服务器以提高性能，这可能具有讽刺意味地解释了您的性能下降的原因.如果是这种情况，您可以尝试将 MAXDOP 提示设置为您之前拥有的处理器数量，看看是否有帮助.

By default, I believe the server-wide setting of MAXDOP is 0, meaning use as many as possible. If you recently upgraded your database server with more processors to help performance, that could ironically explain why your performance is suffering. If that's the case, you might try setting the MAXDOP hint to the number of processors you had before and see if that helps.

这篇关于Sql Server 2000 存储过程防止并行或其他什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！