Online Index Insert
Introduction
The Online Index Insert operator is used to insert rows in a temporary internal index, as part of processing for online index operations. The rows to be inserted are produced by the operator’s child subtree. Unlike regular insert operators (Clustered Index Insert, Columnstore Index Insert, Index Insert, and Table Insert), the Online Index Insert operator does not return any rows to its parent operator.
The target index cannot be a memory-optimized table, but can otherwise be any index type, or heap. If the target is a clustered index or a heap, then the Online Index Insert adds the news rows simultaneously in the internal “temporary mapping index”.
Visual appearance in execution plans
Depending on the tool being used, an Online Index Insert operator is displayed in a graphical execution plan as shown below:
|
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
 |
 |
 |
Algorithm
The basic algorithm for the Online Index Insert operator is as shown below:
Note that this flowchart is a simplification.
Preparation phase
The preparation phase (see also here) executes when the Online Index Insert operator starts execution. During this phase, a snapshot of the original index or heap (if any) is created, and a new, empty index or heap is allocated. This new index or heap is set to write only and is only used by the Online Index Insert operator, and by the Delete, Insert, Merge, or Update operators of any concurrently executing data modification statements.
When the target of the Online Index Insert is a clustered index or a heap, then an additional new, empty nonclustered index is created for the “temporary mapping index”.
During the preparation phase, the operator takes and then releases an S-lock on the table, which means that it has to wait for concurrent update transactions to complete.
Build phase
During the build phase, the Online Index Insert operator receives rows from its child operator and inserts them in the new index or heap that was created during the preparation phase. If the target is a clustered index or a heap, then a copy of the data is also inserted into the “temporary mapping index”.
Acquire and release locks
Before inserting data, the Online Index Insert operator will always acquire the necessary locks (or verify that it already has them). Unlike regular modification operators, Online Index Insert releases these locks when the data has been inserted, so that concurrently executing data modification statements can write their changes in the target index, and in the temporary mapping index if appropriate.
Insert row in index(es)
The Online Index Insert operator inserts the data it receives in the target index. When the target index is a heap or a clustered index, then it also inserts a copy of the relevant columns in the temporary mapping index.
Final phase
When the Online Index Insert operator has processed its entire input, the target index holds the same data as the original data to be rebuilt or changed (if any). At this point, the index that was created in the preparation phase is “renamed” to become the actual index, and the original index (if any) is dropped.
This final phase has to wait until all concurrent update transactions are complete. The Online Index Insert operator may require a short S-lock or Sch-M lock on the table. See here for more details.
Operator properties
The properties below are specific to the Online Index Insert operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Online Index Insert operator are marked with a *.
| Property name | Description |
|---|---|
| Actual Number of Rows * | This is always 0 for the Online Index Insert operator, because it does not return any rows to its parent. |
| Estimated Number of Rows * | This is always equal to the Estimated Number of Rows from the child operator, even though the Online Index Insert operator in reality does not return any rows to its parent. |
| Object | The index or heap that the Online Index Insert operator will insert rows to, using three or four part naming (database, schema, table, index). The subproperties of the Object property represent the three or four name parts separately, but also include these additional properties:
|
| Partitioned | This property is present and set to True when the target of the Online Index Insert is a partitioned index. |
Implicit properties
This table below lists the behavior of the implicit properties for the Online Index Insert operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Online Index Insert operator supports row mode execution only. |
| Blocking | The Online Index Insert operator is non-blocking. |
| Memory requirement | The Online Index Insert operator does not have any special memory requirement. |
| Order-preserving | Since the Online Index Insert operator does not return any rows, there is no order to its output. |
| Parallelism aware | The Online Index Insert operator is not parallelism aware. |
| Segment aware | The Online Index Insert operator is not segment aware. |