Labels

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.

PhaseSource activitySource 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.*







1 comment:
Write comments
  1. Get pleasure from people with regards to reveal. I am hoping you'll definitely reveal once again. Programming for Problem Solving

    ReplyDelete

Please do not enter spam links

Services

More Services