What is Online Indexing in SQL Server 2016

During a simple online index operation, such as creating a clustered index on a nonindexed table (heap), the source and target go through three phases: preparation, build, and final.+

The following illustration shows the process for creating an initial clustered index online. The source object (the heap) has no other indexes. The source and target structure activities are shown for each phase; concurrent user select, insert, update, and delete operations are also shown. The preparation, build, and final phases are indicated together with the lock modes used in each phase.

 The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.+

** The resource lock INDEX_BUILD_INTERNAL_RESOURCE prevents the execution of concurrent data definition language (DDL) operations on the source and pre-existing structures while the index operation is in progress. For example, this lock prevents concurrent rebuild of two indexes on the same table. Although this resource lock is associated with the Sch-M lock, it does not prevent data manipulation statements.

The previous table shows a single Shared (S) lock acquired during the build phase of an online index operation that involves a single index. When clustered and nonclustered indexes are built, or rebuilt, in a single online index operation (for example, during the initial clustered index creation on a table that contains one or more nonclustered indexes) two short-term S locks are acquired during the build phase followed by long-term Intent Shared (IS) locks. One S lock is acquired first for the clustered index creation and when creating the clustered index is completed, a second short-term S lock is acquired for creating the nonclustered indexes. After the nonclustered indexes are created, the S lock is downgraded to an IS lock until the final phase of the online index operation.

Phase	Source activity	Source locks
Preparation Short phase	System metadata preparation to create the new empty index structure. A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency. Concurrent user write operations on the source are blocked for a short period. No concurrent DDL operations are allowed except creating multiple nonclustered indexes.	S (Shared) on the table* IS (Intent Shared) INDEX_BUILD_INTERNAL_RESOURCE**
Build Main phase	The data is scanned, sorted, merged, and inserted into the target in bulk load operations. Concurrent user select, insert, update, and delete operations are applied to both the pre-existing indexes and any new indexes being built.	IS INDEX_BUILD_INTERNAL_RESOURCE**
Final Short phase	All uncommitted update transactions must complete before this phase starts. Depending on the acquired lock, all new user read or write transactions are blocked for a short period until this phase is completed. System metadata is updated to replace the source with the target. The source is dropped if it is required. For example, after rebuilding or dropping a clustered index.	INDEX_BUILD_INTERNAL_RESOURCE** S on the table if creating a nonclustered index.* SCH-M (Schema Modification) if any source structure (index or table) is dropped.*