Configure Parallel Index Operations

This topic defines max degree of parallelism and explains how to modify this setting in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. On multiprocessor computers that are running SQL Server Enterprise or higher, index statements may use multiple processors to perform the scan, sort, and index operations associated with the index statement just like other queries do. The number of processors used to run a single index statement is determined by the max degree of parallelism configuration option, the current workload, and the index statistics. The max degree of parallelism option determines the maximum number of processors to use in parallel plan execution. If the 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. 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.

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.

The MAXDOP index option overrides the max degree of parallelism configuration option only for the query specifying this option. 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

0

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.

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.

Parallel index execution and the MAXDOP index option apply to the following Transact-SQL statements:

CREATE INDEX

ALTER INDEX REBUILD

DROP INDEX (This applies to clustered indexes only.)

ALTER TABLE ADD (index) CONSTRAINT

ALTER TABLE DROP (clustered index) CONSTRAINT

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. For more information, see Partitioned Tables and Indexes.

To set max degree of parallelism on an existing index

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