在 SQL Server 中使用 OPTION(MAXDOP 1) 的目的是什么? [英] What is the purpose for using OPTION(MAXDOP 1) in SQL Server?
问题描述
我从来没有清楚地理解MAXDOP 的用法.我知道它可以加快查询速度，而且它是我可以用于查询优化的最后一项.
I have never clearly understood the usage of MAXDOP. I do know that it makes the query faster and that it is the last item that I can use for Query Optimization.
但是，我的问题是，何时何地最适合在查询中使用?
However, my question is, when and where it is best suited to use in a query?
推荐答案
正如 Kaboing 所提到的，MAXDOP(n) 实际上控制着查询处理器中正在使用的 CPU 内核数.
As Kaboing mentioned, MAXDOP(n) actually controls the number of CPU cores that are being used in the query processor.
在完全空闲的系统上，SQL Server 将尝试尽快将表拉入内存并在内存中连接它们.在您的情况下，可能最好使用单个 CPU 来执行此操作.这可能与使用 OPTION (FORCE ORDER) 的效果相同，后者强制查询优化器使用您指定的连接顺序.在某些情况下，我看到 OPTION (FORCE PLAN) 将查询的执行时间从 26 秒减少到 1 秒.
On a completely idle system, SQL Server will attempt to pull the tables into memory as quickly as possible and join between them in memory. It could be that, in your case, it's best to do this with a single CPU. This might have the same effect as using OPTION (FORCE ORDER) which forces the query optimizer to use the order of joins that you have specified. IN some cases, I have seen OPTION (FORCE PLAN) reduce a query from 26 seconds to 1 second of execution time.
Books Online 继续说 MAXDOP 的可能值是:
Books Online goes on to say that possible values for MAXDOP are:
0 - 根据当前系统工作负载使用可用 CPU 的实际数量.这是默认值和推荐设置.
0 - Uses the actual number of available CPUs depending on the current system workload. This is the default value and recommended setting.
1 - 禁止并行计划生成.操作将串行执行.
1 - Suppresses parallel plan generation. The operation will be executed serially.
2-64 - 将处理器数量限制为指定值.根据当前的工作负载，可以使用更少的处理器.如果指定的值大于可用 CPU 的数量，则使用实际可用的 CPU 数量.
2-64 - Limits the number of processors to the specified value. Fewer processors may be used depending on the current workload. If a value larger than the number of available CPUs is specified, the actual number of available CPUs is used.
我不确定 MAXDOP 的最佳用法是什么，但是我会猜测并说如果你有一个有 8 个分区的表，你会想要指定 MAXDOP(8) 由于 I/O 限制，但我可能是错的.
I'm not sure what the best usage of MAXDOP is, however I would take a guess and say that if you have a table with 8 partitions on it, you would want to specify MAXDOP(8) due to I/O limitations, but I could be wrong.
以下是我找到的一些关于 MAXDOP 的快速链接:
Here are a few quick links I found about MAXDOP:
这篇关于在 SQL Server 中使用 OPTION(MAXDOP 1) 的目的是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
