Index Delete
Introduction
The Index Delete operator is used to delete rows from a nonclustered index. The rows to be deleted are identified by rows returned from the operator’s child subtree; these rows must include at least all columns indexed by the target index. Index Delete 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 Delete or Table Delete operator.
An Index Delete operator that targets a columnstore index is represented in the execution plan XML as a regular Index Delete operator, with the Storage subproperty of the Object property set to ‘ColumnStore’. But SSMS (and most other tools) then represent this operator as a Columnstore Index Delete operator in the graphical execution plan. To match the representation of the execution plan that most people use, I’ll focus the descriptions below on how Index Delete operates when the Storage property is ‘RowStore’, and call out relevant differences on the separate Columnstore Index Delete page.
It is, as far as currently known, not possible for an Index Delete operator to target a memory-optimized index. All execution plans for deleting data from a memory-optimized table use a narrow plan, with just a single Table Delete operator to modify the data for all indexes, per row.
Visual appearance in execution plans
Depending on the tool being used, an Index Delete operator with the Storage property not equal to ColumnStore 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 Delete 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.
Acquire locks
Before deleting data, the Index Delete 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, but 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.
Delete row
The Index Delete operator uses the values for the indexed columns from the input row to find the row to delete in the target index, then deletes the row. No constraints are checked at this time. 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 Delete is invoked by calling its GetNext() method. And like any other operator, it responds my returning a row, with the columns listed in the Output List property. Most of the time these will be values from the input data, though columns included in the index might be added to the Output List as well.
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 Delete 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 Delete operator are marked with a *.
| Property name | Description |
|---|---|
| DMLRequestSort | When set to true, the delete 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 Delete operator will delete rows from, using four part naming (database, schema, table, index). The subproperties of the Object property represent 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 Delete is a partitioned index. |
| 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 Delete 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 Delete operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Index Delete operator supports row mode execution only. |
| Blocking | The Index Delete operator is non-blocking. |
| Memory requirement | The Index Delete operator does not have any special memory requirement. |
| Order-preserving | The Index Delete operator is fully order-preserving. |
| Parallelism aware | The Index Delete 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 Delete operator is not segment aware. |