MSSQL-最大并行度DOP

微软官方文档说

Considerations

• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server professional.

• If the affinity mask option isn't set to the default, it may restrict the number of processors available to SQL Server on symmetric multiprocessing (SMP) systems.

• Setting max degree of parallelism (MAXDOP) to 0 allows SQL Server to use all the available processors up to 64 processors. However, this isn't the recommended value for most cases. For more information on the recommended values for max degree of parallelism, see the Recommendations section in this page.

• To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used during a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

• The max degree of parallelism limit is set per task. It isn't a per request or per query limit. This means that during a parallel query execution, a single request can spawn multiple tasks up to the MAXDOP limit, and each task will use one worker and one scheduler. For more information, see the Scheduling parallel tasks section in the Thread and Task Architecture Guide.

• You can override the max degree of parallelism server configuration value:

  • At the query level, using the MAXDOP query hint or Query Store hint.
  • At the database level, using the MAXDOP database scoped configuration.
  • At the workload level, using the MAX_DOP Resource Governor workload group configuration option.

• Index operations that create or rebuild an index, or that drop a clustered index, can be resource intensive. You can override the max degree of parallelism value for index operations by specifying the MAXDOP index option in the index statement. The MAXDOP value is applied to the statement at execution time and isn't stored in the index metadata. For more information, see Configure Parallel Index Operations.

• In addition to queries and index operations, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP. You can disable parallel execution plans for these statements by using Trace Flag 2528. For more information, see Trace Flags (Transact-SQL).

• SQL Server 2022 (16.x) introduced Degree of Parallelism (DOP) Feedback, a new feature to improve query performance by identifying parallelism inefficiencies for repeating queries, based on elapsed time and waits. DOP feedback is part of the intelligent query processing family of features, and addresses suboptimal usage of parallelism for repeating queries. For information about DOP feedback, visit Degree of parallelism (DOP) feedback.

他们建议

 

Configure the max degree of parallelism (server configuration option) - SQL Server | Microsoft Learn
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16 

 

Making parallelism waits actionable - Microsoft Community Hub
https://techcommunity.microsoft.com/t5/sql-server-blog/making-parallelism-waits-actionable/ba-p/385691