Guidelines for Performing Online Index Operations

  • Article

When you perform online index operations, the following guidelines apply:

  • Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
  • Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.
  • Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.

Note

Online index operations are available only in Microsoft SQL Server 2005 Enterprise Edition.

The following table shows the index operations that can be performed online and the indexes that are excluded from these online operations. Additional restrictions are also included.

Online index operation Excluded indexes Other restrictions

ALTER INDEX REBUILD

Disabled clustered index or disabled indexed view

XML index

Index on a local temp table

Specifying the keyword ALL may cause the operation to fail when the table contains an excluded index.

Additional restrictions on rebuilding disabled indexes apply. For more information, see Guidelines for Disabling Indexes.

CREATE INDEX

XML index

Initial unique clustered index on a view

Index on a local temp table

 

CREATE INDEX WITH DROP_EXISTING

Disabled clustered index or disabled indexed view

Index on a local temp table

XML index

 

DROP INDEX

Disabled index

XML index

Nonclustered index

Index on a local temp table

Multiple indexes cannot be specified within a single statement.

ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE)

Index on a local temp table

Clustered index

Only one subclause is allowed at a time. For example, you cannot add and drop PRIMARY KEY or UNIQUE constraints in the same ALTER TABLE statement.

ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE)

Clustered index

 

The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.

The online option setting (ON or OFF) specified when you create or drop a clustered index is applied to any nonclustered indexes that must be rebuilt. For example, if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also.

When you create or rebuild a UNIQUE index online, the index builder and a concurrent user transaction may try to insert the same key, therefore violating uniqueness. If a row entered by a user is inserted into the new index (target) before the original row from the source table is moved to the new index, the online index operation will fail.

Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server 2005 Database Engine will select the user or application activity as a deadlock victim.

You can perform concurrent online index DDL operations on the same table or view only when you are creating multiple new nonclustered indexes, or reorganizing nonclustered indexes. All other online index operations performed at the same time fail. For example, you cannot create a new index online while rebuilding an existing index online on the same table.

Disk Space Considerations

Generally, disk space requirements are the same for online and offline index operations. An exception is additional disk space required by the temporary mapping index. This temporary index is used in online index operations that create, rebuild, or drop a clustered index. Dropping a clustered index online requires as much space as creating a clustered index online. For more information, see Disk Space Requirements for Index DDL Operations.

Performance Considerations

Although online index operations permit concurrent user update activity, the index operations will take longer if the update activity is very heavy. Typically, online index operations will be slower than equivalent offline index operations regardless of the concurrent update activity level.

Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double. This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Online index operations are fully logged.

Although we recommend online operations, you should evaluate your environment and specific requirements. It may be optimal to run index operations offline. In doing this, users have restricted access to the data during the operation, but the operation finishes faster and uses fewer resources.

On multiprocessor computers that are running SQL Server 2005 Enterprise Edition, index statements may use more processors to perform the scan and sort operations associated with the index statement just like other queries do. You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. In this way, you can balance the resources that are used by index operation with those of the concurrent users. For more information, see Configuring Parallel Index Operations.

Because an S-lock or Sch-M lock is held in the final phase of the index operation, be careful when you run an online index operation inside an explicit user transaction, such as BEGIN TRANSACTION...COMMIT block. Doing this causes the lock to be held until the end of the transaction, therefore impeding user concurrency.

Transaction Log Considerations

Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. For more information, see Transaction Log Disk Space for Index Operations.

See Also

Concepts

How Online Index Operations Work
Performing Index Operations Online

Other Resources

ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Updated content:
  • Removed unique nonclustered index from the list of excluded indexes. This change applies to SQL Server 2005 Service Pack 1 and higher.

5 December 2005
New content:
  • Added unique nonclustered index to the list of excluded indexes for these statements: CREATE INDEX, CREATE INDEX WITH DROP_EXISTING, and ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE).