配置并行索引操作Configure Parallel Index Operations

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

本主题通过使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中定义最大并行度 (max degree of parallelism) 并说明如何修改此设置。This topic defines max degree of parallelism and explains how to modify this setting in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

在运行 SQL ServerSQL Server Enterprise 或更高版本的多处理器系统上,索引语句可能会像其他查询那样,使用多个处理器 (CPU) 来执行与索引语句关联的扫描、排序和索引操作。On multiprocessor systems that are running SQL ServerSQL Server Enterprise or higher, index statements may use multiple processors (CPUs) to perform the scan, sort, and index operations associated with the index statement just like other queries do. 用于运行单个索引语句的 CPU 数由最大并行度服务器配置选项、当前工作负荷以及索引统计信息决定。The number of CPUs used to run a single index statement is determined by the max degree of parallelism server configuration option, the current workload, and the index statistics. max degree of parallelism 选项决定了执行并行计划时使用的最大处理器数。The max degree of parallelism option determines the maximum number of processors to use in parallel plan execution. 如果 SQL Server 数据库引擎SQL Server Database Engine 检测到系统忙,索引操作的并行度将自动降低,然后再开始执行语句。If the SQL Server 数据库引擎SQL Server Database Engine detects that the system is busy, the degree of parallelism of the index operation is automatically reduced before statement execution starts. 如果非分区索引的第一个键列包含有限数量的非重复值,或者每个非重复值的出现频率变化较大, 数据库引擎Database Engine 也可能会降低并行度。The 数据库引擎Database Engine can also reduce the degree of parallelism if the leading key column of a non-partitioned index has a limited number of distinct values or the frequency of each distinct value varies significantly. 有关详细信息,请参阅查询处理体系结构指南For more information, see Query Processing Architecture Guide.

备注

SQL ServerSQL Server 的各版本中均不提供并行索引操作。Parallel index operations are not available in every SQL ServerSQL Server edition. 有关详细信息,请参阅 SQL Server 2016 各个版本支持的功能For more information, see Features Supported by the Editions of SQL Server 2016.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 查询优化器使用的处理器数量通常能够提供最佳的性能。The number of processors that are used by the query optimizer typically provides optimal performance. 但是,有些操作(如创建、重新生成或删除很大的索引)占用大量资源,在索引操作期间会造成没有足够的资源供其他应用程序和数据库操作使用。However, operations such as creating, rebuilding, or dropping very large indexes are resource intensive and can cause insufficient resources for other applications and database operations for the duration of the index operation. 出现此问题时,您可以通过限制用于索引操作的处理器数,手动配置用于运行索引语句的最大处理器数。When this problem occurs, you can manually configure the maximum number of processors that are used to run the index statement by limiting the number of processors to use for the index operation.

  • MAXDOP 索引选项只为指定此选项的查询覆盖 max degree of parallelism 配置选项。The MAXDOP index option overrides the max degree of parallelism configuration option only for the query specifying this option. 下表列出了可为 max degree of parallelism 配置选项和 MAXDOP 索引选项指定的有效整数值。The following table lists the valid integer values that can be specified with the max degree of parallelism configuration option and the MAXDOP index option.

    Value 说明Description
    00 指定服务器根据当前系统工作负荷确定所使用的 CPU 数目。Specifies that the server determines the number of CPUs that are used, depending on the current system workload. 这是默认值,还是推荐设置。This is the default value and recommended setting.
    11 取消生成并行计划。Suppresses parallel plan generation. 操作将以串行方式执行。The operation will be executed serially.
    2-642-64 将处理器的数量限制为指定的值。Limits the number of processors to the specified value. 根据当前工作负荷,可能使用较少的处理器。Fewer processors may be used depending on the current workload. 如果指定的值大于可用的 CPU 数量,将使用实际可用的 CPU 数量。If a value larger than the number of available CPUs is specified, the actual number of available CPUs is used.
  • 并行索引执行和 MAXDOP 索引选项适用于下列 Transact-SQLTransact-SQL 语句:Parallel index execution and the MAXDOP index option apply to the following Transact-SQLTransact-SQL statements:

  • 不能在 ALTER INDEX (...) REORGANIZE 语句中指定 MAXDOP 索引选项。The MAXDOP index option cannot be specified in the ALTER INDEX (...) REORGANIZE statement.

  • 如果查询优化器将并行度应用于生成操作,则需要排序的已分区索引操作的内存需求可能会很大。Memory requirements for partitioned index operations that require sorting can be greater if the Query Optimizer applies degrees of parallelism to the build operation. 并行度越高,内存需求就越大。The higher the degrees of parallelism, the greater the memory requirement is. 有关详细信息,请参阅 Partitioned Tables and IndexesFor more information, see Partitioned Tables and Indexes.

权限 Permissions

要求具有对表或视图的 ALTER 权限。Requires ALTER permission on the table or view.

使用 SQL Server Management StudioUsing SQL Server Management Studio

设置索引的最大并行度To set max degree of parallelism on an index

  1. 在对象资源管理器中,单击加号以展开所需的数据库,其中包含您要设置索引最大并行度的表。In Object Explorer, click the plus sign to expand the database that contains the table on which you want to set max degree of parallelism for an index.

  2. 展开 “表” 文件夹。Expand the Tables folder.

  3. 单击加号以展开您要设置索引的最大并行度的表。Click the plus sign to expand the table on which you want to set max degree of parallelism for an index.

  4. 展开 “索引” 文件夹。Expand the Indexes folder.

  5. 右键单击要为其设置最大并行度的索引,然后选择“属性”。Right-click the index for which you want to set the max degree of parallelism and select Properties.

  6. “选择页” 下,选择 “选项”Under Select a page, select Options.

  7. 选择 “最大并行度”,然后输入 1 和 64 之间的某个值。Select Maximum degree of parallelism, and then enter some value between 1 and 64.

  8. 单击“确定”。Click OK.

使用 Transact-SQLUsing Transact-SQL

设置现有索引的最大并行度To set max degree of parallelism on an existing index

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;   
    GO  
    /*Alters the IX_ProductVendor_VendorID index on the Purchasing.ProductVendor table so that, if the server has eight or more processors, the Database Engine will limit the execution of the index operation to eight or fewer processors.  
    */  
    ALTER INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor  
    REBUILD WITH (MAXDOP=8);   
    GO  
    

有关详细信息,请参阅 ALTER INDEX (Transact-SQL)For more information, see ALTER INDEX (Transact-SQL).

设置新建索引的最大并行度Set max degree of parallelism on a new index

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    CREATE INDEX IX_ProductVendor_NewVendorID   
    ON Purchasing.ProductVendor (BusinessEntityID)  
    WITH (MAXDOP=8);  
    GO  
    

另请参阅See also

查询处理体系结构指南 Query Processing Architecture Guide
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
DROP INDEX (Transact-SQL) DROP INDEX (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER TABLE table_constraint (Transact-SQL) ALTER TABLE table_constraint (Transact-SQL)
ALTER TABLE index_option (Transact-SQL)ALTER TABLE index_option (Transact-SQL)