Parallel Operations Creating Indexes

The query plans built for the creation of indexes allow parallel, multi-threaded index create operations on computers with multiple microprocessors.

Microsoft® SQL Server™ 2000 uses the same algorithms to determine the degree of parallelism (the total number of separate threads to run) for create index operations as it does for other Transact-SQL statements. The only difference is that the CREATE INDEX, CREATE TABLE, or ALTER TABLE statements that create indexes do not support the MAXDOP query hint. The maximum degree of parallelism for an index creation is subject to the max degree of parallelism server configuration option, but you cannot set a different MAXDOP value for individual index creation operations.

When SQL Server 2000 builds a create index query plan, the number of parallel operations is set to the lowest value of:

• 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 with eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel threads are generated for an index creation. If five of the CPUs in the computer exceed the threshold of SQL Server work when an index creation execution plan is built, the execution plan specifies only three parallel threads.

The main phases of parallel index creation include:

• 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 4 million rows in the table, and the degree of parallelism is 4, the coordinating thread will determine the key values that delimit 4 sets of rows with 1 million rows in each set.
  
  
• 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. 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.

Individual CREATE TABLE or ALTER TABLE statements can have multiple constraints that require the creation of an index. These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer with multiple CPUs.

See Also

tempdb and Index Creation