Online Index Operation
The online index operation can be divided into three phases Preparation, Build and Final which are explained in detail
During the preparation phase, the following activities take place:
- A snapshot of the source table is acquired. When taking a snapshot of the table, a shared lock is obtained on the table. This is done for transactional level read consistency.
- An Intent share lock is also obtained. This lock is maintained until the final phase.
- A resource lock INDEX_BUILD_INTERNAL_RESOURCE is acquired to prevent concurrent DDL operations
- Metadata is created. The logical metadata consists of Index ID, Index name, keys, and attributes
During the build phase:
- The new index structure created in the preparation phase is populated with the sorted data from existing data source.
- The shared lock acquired in the preparation phase is released. Concurrent DML operations can be performed during the Build phase.
- If the clustered index is created or rebuilt online, a temporary nonclustered index, called the mapping index, is created in the build phase.
When a clustered index is created or dropped and non-clustered indexes exist for that table, the preparation and build phases are repeated twice; once for the new clustered index and again for the nonclustered indexes
The Final phase is used to inform the system that the index is ready. These are the following activities that take place during this phase:
- The IS lock is upgraded to a Schema Modify (Sch-M) or Shared(S) lock depending on the type of index operation.
- If a clustered index is created, then all the nonclustered indexes are rebuilt. Due to the locks held on the table, concurrent DML operations are not allowed on the table at this time.
- The Sch-M or S lock is released once the index is in the ready state.
Online indexing can be performed through the following T-SQL statement as shown below
CREATE INDEX IX_Patient on PDS.Patient (Identifier) (ONLINE=ON)
Best PracticesThe following points should be considered before using the online indexing feature
- Backup the transaction log and truncate it before running large-scale index operations online.
- It is recommended to have the SORT_IN_TEMPDB option set to ON. This will separate the index operations and the user transactions.
- Have the recovery model set to SIMPLE or bulk logged so that minimal logging of index transactions takes place.
- Do not run the online index operation in an explicit transaction. The log of the user database cannot be truncated until the explicit transaction ends.
The Online Indexing feature is available only in the Enterprise Edition of SQL Server 2005.