Index Update
Introduction
The Index Update operator is primarily used to update rows in a nonclustered index, but can also insert and delete rows in that same index. The rows to be affected, as well as the required action and the new values of all columns in the index, are identified by rows returned from the operator’s child subtree. Index Update operators are typically used in wide update plans only; when the optimizer chooses to use a narrow update plan, it will modify all nonclustered indexes in the Clustered Index Update or Table Update operator.
It is, as far as currently known, not possible for an Index update operator to target a nonclustered columnstore index. All execution plans for updating data in a table that has a nonclustered columnstore index use either a narrow plan, or a mixed plan where at least the nonclustered columnstore index is modified by the Clustered Index Update or Table Update operator.
It is, as far as currently known, also not possible for an Index update operator to target a memory-optimized index. All execution plans for updating data in a memory-optimized table use a narrow plan, with just a single Table Update operator to modify the data for all indexes, per row.
Visual appearance in execution plans
Depending on the tool being used, an Index Update 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 Index Update operator is as shown below:
Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.
Action Column
The Index Update operator may optionally have its Action Column property set. If so, then this lists the name of one of the columns in the input data that will be interpreted as specifying, per individual row, what type of modification is needed (insert, update, or delete).
The known values and their meaning for the action column (for any operator, not just for Index Update) are:
- 1: Update
- 3: Delete
- 4: Insert
It is currently unknown whether there are more possible values.
If the Action Column property is not provided, then all input rows will be processed as updates to an existing row.
Acquire locks
Before deleting data, the Index Update operator will always acquire the necessary locks (or verify that it already has them). Which locks exactly are taken depends on the isolation level of the transaction, as well as the action column (if any). It is never possible to completely eliminate all locking on data modifications.
This operator does not release the locks it takes, nor does any other operator in the same execution plan. Locks for data modifications are always held until the end of the transaction.
A full discussion of locking strategies used for the various transaction isolation levels is beyond the scope of this website.
Insert row
When the action column indicates that a row needs to be inserted, the Index Update operator uses the Predicate property to find the values for all columns in that row. The Index Update operator does not actively check any constraints. Primary keys and unique constraints are implicitly checked because the storage engine throws an error as soon as data is written to a unique index that would cause a duplicate entry. For other constraints, the optimizer adds additional operators to gather relevant data, then checks the validity of the data in an Assert operator.
Update row
When the action column is absent or indicates that a row needs to be updated, the Index Update operator uses the values provided for the index key columns in the Predicate property to locate the row. If the target index is not declared as unique, then the clustered index key columns are considered part of the index key for this index. The remaining columns in the Predicate property then provide the new values to assign to the other columns in that index row. This means that the Index Update always does an update in place; the row will never move to another location in the index because the index columns are not changed. If a query can change the indexed columns of a nonclustered index, then either that index is updated within the Clustered Index Update or Table Update operator (narrow plan), or the optimizer uses a Split operator to convert these updates to equivalent insert and delete operations (optionally combined with a Collapse operator for better performance).
The optimizer ensures that foreign key and check constraints are checked by other operators in the execution plan. An Assert operator is typically used to force a rollback of the operation if a constraint would be violated.
Delete row
When the action column indicates that a row needs to be deleted, the Index Update operator uses the values provided for the index key columns in the Predicate property to locate the row, and then deletes it from the index. If the target index is not declared as unique, then the clustered index key columns are considered part of the index key for this index. The data for non-key columns in the Predicate property is ignored for rows that have to be deleted. No constraints are checked by the operator; the optimizer ensures that foreign key and check constraints are checked by other operators in the execution plan. An Assert operator is typically used to force a rollback of the operation if a constraint would be violated.
Return row
Like any operator, Index Update is invoked by calling its GetNext() method. And like any other operator, it responds by returning a row, with the columns listed in the Output List property. Most of the time these will be values from the input data. It might be possible that the “old” data in included columns (before updating or deleting a row) can be read and returned as well, but I have never actually seen this in any execution plan.
The output rows can be used by parent operators for additional logic, such as constraint checking or propagating the changes to other indexes, indexed views, etc.
If the parent operator does not need any data, the Output List property is empty and an empty row is returned for each row deleted.
Operator properties
The properties below are specific to the Index Update 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 Index Update operator are marked with a *.
| Property name | Description |
|---|---|
| ActionColumn | When this property is present, it lists the name of the column from the input that determines for each input row whether it updates index data in place, inserts new data in the index, or deletes an existing row from the index. When this property is missing, all input rows are considered to be an update in place. |
| DMLRequestSort | When set to true, the update operation might qualify for minimal logging if additional conditions apply. Details of those additional conditions and the requirements for DMLRequestSort to be true can be found here. |
| Object | The index that the Index Update operator will modify, using four part naming (database, schema, table, index). The subproperties of the Object property represent the alias and the four name parts separately, but also include these additional properties:
|
| Partitioned | This property is present and set to True when the target of the Index Update is a partitioned index. |
| Predicate | Maps columns from the input stream to the columns in the index indicated by the Object property, or sets these columns to hardcoded values or variables from the query text. For input rows that are to be processed as a delete (as determined by the data in the column listed in the Action Column property), only the columns that are part of the index key are used. (Plus additional clustered index key columns if the index is not declared as unique). |
| WithOrderedPrefetch | This property is present (and set to true) when prefetching is requested. This requires the input data to be ordered in the order of the index. Exact details of prefetching in the context of an Index Update operator are unknown at this time. The assumed effect of prefetching is that the operator issues an asynchronous read request to the storage system, so that read-ahead reading can be used to more efficiently find the rows to be deleted. Rows are still processed in the order in which the operator reads them, but they will already be in the buffer pool as a result of the read ahead prefetching. When this property is not present, no prefetching is used. |
Implicit properties
This table below lists the behavior of the implicit properties for the Index Update operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Index Update operator supports row mode execution only. |
| Blocking | The Index Update operator is non-blocking. |
| Memory requirement | The Index Update operator does not have any special memory requirement. |
| Order-preserving | The Index Update operator is fully order-preserving. |
| Parallelism aware | The Index Update operator does not support parallelism. It can only be used in a serial plan, or in a serial section of a parallel plan. |
| Segment aware | The Index Update operator is not segment aware. |