SQL Server中最大并行度详解

在 SQL Server 中，MAXDOP（Maximum Degree of Parallelism，最大并行度）是一个用于控制并行查询执行中最大可用 CPU 核心数的配置参数。通过设置 MAXDOP，你可以管理 SQL Server 在执行并行查询时使用多少个处理器核心，从而优化查询性能，特别是在多核处理器的系统上。

1. 基本概念

MAXDOP 参数控制的是 SQL Server 在执行查询时允许使用的最大处理器核心数。当一个查询或操作可以并行化时，SQL Server 将查询分解为多个子任务，并分配到多个 CPU 核心上运行。通过设置 MAXDOP，你可以指定 SQL Server 使用的最大核心数，防止系统过度并行化导致资源争用或性能下降。

并行查询：SQL Server 会根据查询的复杂性、表的大小、索引等因素决定是否并行执行查询。如果查询操作的成本较高（如全表扫描、大量聚合操作等），SQL Server 可能会选择并行执行查询。
核心数：MAXDOP 限制了 SQL Server 在并行查询时使用的最大 CPU 核心数。默认情况下，SQL Server 会根据机器的核心数自动决定并行度，但你可以通过设置 MAXDOP 来调整这一行为。

2. 语法

你可以通过以下几种方式设置 MAXDOP：

a. 在查询中设置 `MAXDOP`

在执行查询时，你可以通过查询提示（Query Hint）来为单个查询指定 MAXDOP。这样做可以控制该查询在执行时使用的最大核心数。

SELECT * FROM large_table OPTION (MAXDOP 4); -- 使用最多4个核心来执行查询

b. 在数据库级别配置 `MAXDOP`

你可以通过以下命令设置 MAXDOP 参数来全局控制 SQL Server 数据库的并行度：

-- 设置最大并行度为4个核心 
sp_configure 'max degree of parallelism', 4; 
RECONFIGURE;

c. 在服务器级别配置 `MAXDOP`

MAXDOP 还可以在 SQL Server 实例的配置中设置，用来控制所有查询的并行度。设置服务器级别的 MAXDOP 会影响所有数据库的查询。

-- 设置实例级别最大并行度为2个核心 
sp_configure 'max degree of parallelism', 2; 
RECONFIGURE;

3. 默认值和常见设置

默认值：SQL Server 的默认 MAXDOP 值是 0，这意味着 SQL Server 会根据硬件的 CPU 核心数自动决定并行度。如果系统有多个核心，SQL Server 将使用多个核心执行并行查询，但它不会超过最大物理核心数。
推荐值：推荐的 MAXDOP 值通常取决于系统的硬件配置。以下是一些建议：
- 如果 SQL Server 实例运行在 4 核或更少的系统上，可以将 MAXDOP 设置为 0（即自动选择并行度），让 SQL Server 根据硬件自动调整。
- 对于具有 8 核或更多的系统，可以将 MAXDOP 设置为 4 或 8，以平衡并行度和系统负载。如果设置过高，可能会引起系统资源争用，影响性能。
- 注意：一些查询可能并行化后变得更加低效，尤其是在小型系统或少量数据的情况下。过多的并行查询可能导致上下文切换和 CPU 资源过度分配，导致性能下降。

4. 工作原理

当 SQL Server 执行一个需要并行化的查询时，查询的成本（通常由查询优化器估算）决定了是否使用并行执行。如果选择并行执行，SQL Server 将把查询分割成多个部分，并将这些部分分配给不同的 CPU 核心来并行处理。

查询分解：SQL Server 根据查询的类型（如全表扫描、大范围连接、复杂聚合等）决定是否进行并行处理，并且会将查询拆分为多个“线程”或任务。这些任务在不同的 CPU 核心上并行执行。
任务调度：每个任务执行后会将结果发送回主任务，最后由主任务汇总所有结果并返回给用户。
最大核心数：MAXDOP 控制这些任务可以使用的最大核心数。如果一个查询成本很高，MAXDOP 限制了最大核心数，避免过多的核心同时执行导致资源争用和系统性能下降。