合 MSSQL之单个SQL所能使用的CPU资源配置（并行配置）

什么是并行处理？

并行查询处理是通过使用多个CPU的功能来减少查询的响应时间的好选择。

并行处理旨在将大任务分解为多个小任务，这些小任务将由离散线程完成。 在这种方法中，单位时间内将执行多个任务。 因此，响应时间将大大减少。 对于SQL Server，这种想法不会改变。 它尝试以并行方式处理需要大量工作量的查询。 生成并行查询计划时，SQL Server中的查询优化器会考虑三个设置。 这些是：

• Cost Threshold for Parallelism 并行成本阈值
• Max Degree of Parallelism (MAXDOP) 最大并行度（MAXDOP）
• Affinity mask 亲和力面膜

并行成本阈值 (COST THRESHOLD FOR PARALLELISM)

估算查询成本是使用查询的I / O和CPU需求计算得出的单位。 此度量有助于优化器评估查询计划的成本并选择最佳计划。 查询优化器通过将查询计划中各个运算符的估计成本相加来计算查询的估计成本。 估计子树成本（估计子树大小）属性指示查询计划中计划的估计成本。

当该值超过“ 并行性成本阈值”设置时，查询优化器将开始考虑在并行计划旁边创建并行查询计划。 此设置的默认值为5，可以使用SQL Server Management Studio或Transact-SQL进行更改。

ENABLE_PARALLEL_PLAN_PREFERENCE查询提示会强制查询优化器生成并行查询计划，而无需考虑并行性的成本阈值。

并行参数配置

使用SQL配置

使用SQL配置过程，该设置立即生效（无需重新启动 MSSQLSERVER 服务）：

使用SSMS图形界面配置 选项

这些选项可更改实例的 MAXDOP。

1. 在“对象资源管理器”中，右键单击所需的实例，然后选择“属性”。
2. 选择“高级”节点。
3. 在 “最大并行度” 框中，选择执行并行计划时所使用的最大处理器数。

配置 max degree of parallelism 最大并行度 服务器配置选项

最大并行度 ，也称为MAXDOP ，是服务器，数据库或查询级别的选项，用于确定执行查询时可以使用的逻辑处理器的最大数量。 默认情况下，此选项设置为0，这意味着查询引擎可以使用所有可用的处理器。 我们可以在“服务器属性”页面上的“ 高级”设置下找到该值。

另一方面，从SQL Server 2017开始，可以为特定数据库更改此值，并且可以在数据库的“ 选项”选项卡上看到该值。

本主题说明如何使用 SQL Server Management Studio、Azure Data Studio 或 Transact-SQL 在 SQL Server 中配置“max degree of parallelism (MAXDOP)”服务器配置选项。 当 SQL Server 实例在具有多个微处理器或 CPU 的计算机上运行时，数据库引擎 会检测是否可以使用并行。 并行度为每次并行计划的执行设置运行单个语句时要使用的处理器数。 您可以使用 max degree of parallelism 选项来限制执行并行计划时所用的处理器数量。 有关 max degree of parallelism (MAXDOP) 所设置的限制的详细信息，请参阅此页中的注意事项部分。 SQL Server 考虑为查询、索引数据定义语言 (DDL) 操作、并行插入、联机更改列、并行统计信息集合以及静态的和由键集驱动的游标填充实施并行执行计划。

备注

SQL Server 2019 (15.x) 介绍了有关在安装过程中如何基于可用处理器数量设置 MAXDOP 服务器配置选项的自动建议。 安装程序用户界面允许接受建议的设置或输入自己的值。 有关详细信息，请参阅“数据库引擎配置 - MaxDOP”页。

在 Azure SQL 数据库和 Azure SQL 托管实例中，每个新的单一数据库、弹性池数据库以及托管实例的默认 MAXDOP 设置都是 8。 在 Azure SQL 数据库中，MAXDOP 数据库范围的配置设置为 8。 在 Azure SQL 托管实例中，max degree of parallelism (MAXDOP) 服务器配置选项设置为 8。

有关 Azure SQL 数据库 中 MAXDOP 的详细信息，请参阅在 Azure SQL 数据库中配置最大并行度 (MAXDOP)。

注意事项

• 此选项是一个高级选项，仅应由有经验的数据库管理员或认证的 SQL Server 专业人员更改。
• 如果 affinity mask 选项不设置为默认值，则可能会限制可用于对称多处理 (SMP) 系统上的 SQL Server 的处理器数。
• 若将最大并行度 (MAXDOP) 设置为 0，SQL Server 将能够使用至多 64 个可用的处理器。 但在大多数情况下，不推荐使用此值。 有关最大并行度的建议值的详细信息，请参阅此页中的建议部分。
• 若要取消生成并行计划，请将 max degree of parallelism 设置为 1。 将该值设置为 1 到 32,767 之间的数值来指定执行单个查询所使用的最大处理器核数。 如果指定的值比可用的处理器数大，则使用实际可用数量的处理器。 如果计算机只有一个处理器，则将忽略 max degree of parallelism 值。
• 将按任务设置最大并行度限制。 它不是按请求限制或按查询限制。 这意味着在并行查询执行期间，一个请求可生成多个任务（不超过 MAXDOP 限制），并且每个任务将使用一个工作线程和一个计划程序。 有关详细信息，请参阅线程和任务体系结构指南中的“计划并行任务”部分。
• 可重写“最大并行度”服务器配置值：
  • 在查询级别，使用 MAXDOP 查询提示或查询存储提示。
  • 在数据库级别，请使用 MAXDOP 数据库范围的配置。
  • 在工作负荷级别，请使用 MAX_DOP Resource Governor 工作负荷组配置选项。
• 索引操作（如创建或重新生成索引、或删除聚集索引）可能会大量占用资源。 您可以通过在索引语句中指定 MAXDOP 索引选项来覆盖索引操作的 max degree of parallelism 值。 MAXDOP 值在执行时应用于语句，但不存储在索引元数据中。 有关详细信息，请参阅 配置并行索引操作。
• 除了查询和索引操作之外，此选项还控制 DBCC CHECKTABLE、DBCC CHECKDB 和 DBCC CHECKFILEGROUP 的并行。 使用跟踪标志 2528，可以禁用为这些语句所做的并行执行计划。 有关详细信息，请参阅跟踪标志 (Transact-SQL)。

建议

从 SQL Server 2016 (13.x) 开始，在服务启动期间，如果 数据库引擎 在启动时检测到每个 NUMA 节点或插槽内的物理内核数目超过 8 个，在默认情况下就会自动创建 soft-NUMA 节点。 数据库引擎 将相同物理内核中的逻辑处理器放入不同的 soft-NUMA 节点中。

下表中的建议旨在将并行查询的所有工作线程保持在相同 soft-NUMA 节点中。 这将提高跨工作负荷 NUMA 节点查询和分布工作线程的性能。 有关详细信息，请参阅 Soft-NUMA。

从 SQL Server 2016 (13.x) 开始，请使用以下准则配置 max degree of parallelism 服务器配置值：

备注：

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。