Parallel Index Operations
The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-threaded operations on computers that have multiple microprocessors.
Note
Parallel index operations are only available in SQL Server 2005 Enterprise Edition.
SQL Server 2005 uses the same algorithms to determine the degree of parallelism (the total number of separate threads to run) for index operations as it does for other queries. The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.
When the SQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:
- The number of microprocessors, or CPUs in the computer.
- The number specified in the max degree of parallelism server configuration option.
- The number of CPUs not already over a threshold of work performed for SQL Server threads.
For example, on a computer that has eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel threads are generated for an index operation. If five of the CPUs in the computer exceed the threshold of SQL Server work when an index execution plan is built, the execution plan specifies only three parallel threads.
The main phases of a parallel index operation include the following:
- A coordinating thread quickly and randomly scans the table to estimate the distribution of the index keys. The coordinating thread establishes the key boundaries that will create a number of key ranges equal to the degree of parallel operations, where each key range is estimated to cover similar numbers of rows. For example, if there are four million rows in the table and the degree of parallelism is 4, the coordinating thread will determine the key values that delimit four sets of rows with 1 million rows in each set. If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
- The coordinating thread dispatches a number of threads equal to the degree of parallel operations and waits for these threads to complete their work. Each thread scans the base table using a filter that retrieves only rows with key values within the range assigned to the thread. Each thread builds an index structure for the rows in its key range. In the case of a partitioned index, each thread builds a specified number of partitions. Partitions are not shared among threads. For more information about how an index is built, see tempdb and Index Creation.
- After all the parallel threads have completed, the coordinating thread connects the index subunits into a single index. This phase applies only to offline index operations.
Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require that an index be created. These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer that has multiple CPUs.
See Also
Concepts
tempdb and Index Creation
Configuring Parallel Index Operations
Degree of Parallelism
Other Resources
ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)
DROP INDEX (Transact-SQL)
ALTER TABLE (Transact-SQL)