Configuring Parallel Index Operations
On multiprocessor computers that are running SQL Server Enterprise, 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 configuration option max degree of parallelism, 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.
Note
Parallel index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.
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 specifying the MAXDOP index option and 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 |
Uses the actual number of available CPUs 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
All semantic rules that are used with max degree of parallelism configuration option are applicable when the MAXDOP index option is used. For more information, see max degree of parallelism Option.
When you execute ALTER INDEX REORGANIZE with or without LOB_COMPACTION, the max degree of parallelism value is a single threaded operation. The MAXDOP index option cannot be specified in the ALTER INDEX REORGANIZE statement.
Online Index Operations
Online index operations allow for concurrent-user activity during the index operation. You can use the MAXDOP option to control the maximum number of processors dedicated to the online index operation. In this way, you can balance the resources used by index operation with those of the concurrent users. For more information, see Performing Index Operations Online.
Partition Index Operations
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 Special Guidelines for Partitioned Indexes.
Examples
The following example creates the index IX_ProductVendor_VendorID on the ProductVendor table and sets the max degree of parallelism option to 8. Assuming the server has eight or more processors, the Database Engine will limit the execution of the index operation to eight or fewer processors.
USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_ProductVendor_VendorID')
DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (VendorID)
WITH (MAXDOP=8);
GO