在 SQL Server 中使用 OPTION(MAXDOP 1) 的目的是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/163917/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the purpose for using OPTION(MAXDOP 1) in SQL Server?
提问by user21968
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.
一直没搞清楚MAXDOP的用法。我知道它使查询更快，并且它是我可以用于查询优化的最后一项。
However, my question is, when and where it is best suited to use in a query?
但是，我的问题是，何时何地最适合在查询中使用？
回答by Jeremiah Peschka
As Kaboing mentioned, MAXDOP(n)actually controls the number of CPU cores that are being used in the query processor.
正如 Kaboing 所提到的，MAXDOP(n)实际上控制了查询处理器中正在使用的 CPU 内核的数量。
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.
在完全空闲的系统上，SQL Server 将尝试尽快将表拉入内存并在内存中连接它们。在您的情况下，可能最好使用单个 CPU 来执行此操作。这可能与 using OPTION (FORCE ORDER)which 强制查询优化器使用您指定的连接顺序具有相同的效果。在某些情况下，我已经看到OPTION (FORCE PLAN)将查询的执行时间从 26 秒减少到 1 秒。
Books Online goes on to say that possible values for MAXDOPare:
Books Online 继续说可能的值MAXDOP是：
0 - Uses the actual number of available CPUs depending on the current system workload. This is the default value and recommended setting.
1 - Suppresses parallel plan generation. The operation will be executed serially.
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.
0 - 根据当前系统工作负载使用可用 CPU 的实际数量。这是默认值和推荐设置。
1 - 禁止并行计划生成。该操作将串行执行。
2-64 - 将处理器数量限制为指定值。根据当前的工作负载，可以使用更少的处理器。如果指定的值大于可用 CPU 的数量，则使用实际可用的 CPU 数量。
I'm not sure what the best usage of MAXDOPis, 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是什么，但是我会猜测并说如果你有一个有 8 个分区的表，你会MAXDOP(8)因为 I/O 限制而想要指定，但我可能是错的。
Here are a few quick links I found about MAXDOP:
以下是我找到的一些快速链接MAXDOP：
Books Online: Degree of Parallelism
回答by Jonas Lincoln
This is a general rambling on Parallelism in SQL Server, it might not answer your question directly.
这是对 SQL Server 中并行性的一般性漫谈，它可能无法直接回答您的问题。
From Books Online, on MAXDOP:
来自 MAXDOP 在线书籍：
Sets the maximum number of processors the query processor can use to execute a single index statement. Fewer processors may be used depending on the current system workload.
设置查询处理器可用于执行单个索引语句的最大处理器数。根据当前系统工作负载，可以使用更少的处理器。
See Rickie Lee's blogon parallelism and CXPACKET wait type. It's quite interesting.
请参阅Rickie Lee关于并行性和 CXPACKET 等待类型的博客。这很有趣。
Generally, in an OLTP database, my opinion is that if a query is so costly it needs to be executed on several processors, the query needs to be re-written into something more efficient.
通常，在 OLTP 数据库中，我的观点是，如果查询成本如此之高以至于需要在多个处理器上执行，则需要将查询重新编写为更有效的内容。
Why you get better results adding MAXDOP(1)? Hard to tell without the actual execution plans, but it might be so simple as that the execution plan is totally different that without the OPTION, for instance using a different index (or more likely) JOINing differently, using MERGE or HASH joins.
为什么添加 MAXDOP(1) 会获得更好的结果？没有实际的执行计划很难说，但它可能很简单，因为执行计划与没有 OPTION 的情况完全不同，例如使用不同的索引（或更可能）以不同的方式加入，使用 MERGE 或 HASH 连接。
回答by Paul
As something of an aside, MAXDOP can apparently be used as a workaround to a potentially nasty bug:
顺便说一句，MAXDOP 显然可以用作解决潜在令人讨厌的错误的方法：
回答by Joshua
There are a couple of parallization bugs in SQL server with abnormal input. OPTION(MAXDOP 1) will sidestep them.
SQL server 中有几个并行化错误，输入异常。OPTION(MAXDOP 1) 会回避他们。
EDIT: Old. My testing was done largely on SQL 2005. Most of these seem to not exist anymore, but every once in awhile we question the assumption when SQL 2014 does something dumb and we go back to the old way and it works. We never managed to demonstrate that it wasn't just a bad plan generation on more recent cases though since SQL server can be relied on to get the old way right in newer versions. Since all cases were IO bound queries MAXDOP 1 doesn't hurt.
编辑：旧的。我的测试主要是在 SQL 2005 上完成的。其中大部分似乎不再存在，但每隔一段时间我们就会质疑 SQL 2014 做了一些愚蠢的事情时的假设，我们又回到了旧的方式并且它起作用了。我们从未设法证明它不仅仅是在最近的情况下生成错误的计划，因为可以依靠 SQL Server 在较新版本中获得正确的旧方法。由于所有情况都是 IO 绑定查询，因此 MAXDOP 1 不会受到影响。
回答by LCJ
Adding my two cents, based on a performance issue I observed.
根据我观察到的性能问题，加上我的两分钱。
If simple queries are getting parellelized unnecessarily, it can bring more problems than solving one. However, before adding MAXDOP into the query as "knee-jerk" fix, there are some server settings to check.
如果简单的查询被不必要地并行化，它会带来比解决一个更多的问题。但是，在将 MAXDOP 添加到查询中作为“膝跳”修复之前，需要检查一些服务器设置。
In Jeremiah Peschka - Five SQL Server Settings to Change, MAXDOP and "COST THRESHOLD FOR PARALLELISM" (CTFP) are mentioned as important settings to check.
在Jeremiah Peschka - 要更改的五个 SQL Server 设置中，MAXDOP 和“并行的成本阈值”( CTFP) 被提及为要检查的重要设置。
Note: Paul White mentioned max server memoryaslo as a setting to check, in a response to Performance problem after migration from SQL Server 2005 to 2012. A good kb article to read is Using large amounts of memory can result in an inefficient plan in SQL Server
注意：Paul White 提到max server memoryaslo 作为要检查的设置，以响应从 SQL Server 2005 迁移到 2012 后的性能问题。一篇很好的知识库文章是使用大量内存会导致 SQL Server 中的计划效率低下
Jonathan Kehayias - Tuning ‘cost threshold for parallelism' from the Plan Cachehelps to find out good value for CTFP.
Jonathan Kehayias - 从计划缓存中调整“并行成本阈值”有助于找出CTFP.
Why is cost threshold for parallelism ignored?
Aaron Bertrand - Six reasons you should be nervous about parallelismhas a discussion about some scenario where MAXDOP is the solution.
Aaron Bertrand - 您应该对并行性感到紧张的六个原因讨论了 MAXDOP 是解决方案的某些场景。
Parallelism-Inhibiting Components are mentioned in Paul White - Forcing a Parallel Query Execution Plan
并行抑制组件在Paul White - Forces a Parallel Query Execution Plan中提到
