Clustered Index Delete
Introduction
The Clustered Index Delete operator is used to delete rows from a clustered index, and optionally (especially in a narrow update plan) from one or more nonclustered indexes as well. The rows may be provided by the operator’s child subtree, or may be identified by the Clustered Index Delete operator itself. In the former case the input rows must include at least all columns indexed by the target index. In the latter case the rows to be deleted are specified by the Seek Predicate property. When deleting a row, the operator can return data from both that row as well as its input row (if any) to its parent.
The Clustered Index Delete operator is in most ways identical in functionality to the (nonclustered) Index Delete operator. One key difference, though, is that a Clustered Index Delete can optionally delete data from more than one index, when multiple objects are specified in its Object property. Even though in that case the operator targets both clustered and nonclustered indexes, it is still represented as a Clustered Index Delete in the execution plan.
A second very important difference is that for the clustered index only, the actual removal of the row is not done when the operator processes the row, but only after it returns and then later regains control. This means that the parent operator receives data about a deleted row, but at that time the row actually still exists in the clustered index.
A Clustered Index Delete operator that targets a clustered columnstore index and no additional nonclustered indexes is represented in SSMS (and most other tools) 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 Clustered Index Delete operates when the Storage property is ‘RowStore’, and call out relevant differences on the separate Columnstore Index Delete page.
Visual appearance in execution plans
Depending on the tool being used, a Clustered Index Delete operator (except when it has a single Object with its Storage property 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 Clustered 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.
Read first row / Read next row
The Clustered Index Delete operator has two different methods of reading the rows to be deleted.
If the operator has a child operator and the Seek Predicate property is absent, then rows to be deleted are found by executing GetNext() on the child operator. The returned row will at least contain all the columns of the target clustered index (including the uniquifier if any). Those key columns can be used to identify the row in the clustered index, read it (using the same logic as a Clustered Index Seek in singleton seek mode) if needed to find key values of other nonclustered indexes to delete and of columns in the Output List property, and then delete it.
If the operator has no child operator and the Seek Predicate property is provided, then that Seek Predicate property is used to read all rows to be deleted, using the same logic as the Clustered Index Seek operator (as singleton seek if the Seek Predicate is guaranteed to identify at most one row; or as range seek otherwise).
Acquire locks
Before deleting data, the Clustered 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 Clustered Index Delete operator uses the values for the indexed columns from the input row or from the row to be deleted to locate 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.
While rows from the (optional) nonclustered indexes are removed immediately, just as in the (nonclustered) Index Delete operator, rows from the clustered index are only locked but not deleted when the deletion of that row processes; they are then actually deleted when the operator is invoked again, before the next row is located and locked for deletion. The effect is that the parent operator(s) can still access the row in the clustered index when processing the data returned by the Clustered Index Delete. See here for more information.
Note that this also means that the Clustered Index Delete has to check for a pending row to be deleted when its Close() method is called. If it’s closed after reaching the end of the input data, this will not be the case. But for instance a Top operator upstream of the Clustered Index Delete can cause the Close() method to be called before the end of the input data, and in that case the last row processed will still be locked but not yet deleted from the clustered index.
More objects?
The Object property of the Clustered Index Delete can hold one or more values. The first is always a clustered index. If there are more, then the rest are all nonclustered indexes on the same table, and rows deleted from the clustered index are “simultaneously” deleted in those nonclustered indexes as well. This is typical for a narrow update plan.
As explained above, the key values of for the extra indexes in the Object property do not need to be in the input. The Clustered Index Delete operator can fetch these from the data in the clustered index when it reads the row to be deleted.
Return row
Like any operator, Clustered 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 any other columns from the clustered 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. As mentioned before, when the parent operator processes this row, the data is locked but still present in the clustered index, but already actually deleted from any nonclustered indexes in the Object property.
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 first object listed is the clustered index that the Clustered Index Delete operator will delete rows from, using four part naming (database, schema, table, index). Optionally, one or more additional values may be supplied; these are nonclustered indexes on the same table. They, too, are specified using four part naming. 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 Clustered Index Delete is a partitioned index. |
| Seek Predicate | A collection object that contains a single Seek Keys element, used to navigate the index and locate the rows to be deleted. A full description of the Seek Keys element can be found on the Index Seek page. If the Seek Predicate property is present, the Clustered Index Delete will always be a leaf node in the execution plan. If it is not, then the operator will always have a child operator. |
| 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 a Clustered 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. As far as currently known, prefetching is never used when the Seek Predicate property is used. |
Implicit properties
This table below lists the behavior of the implicit properties for the Clustered Index Delete operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Clustered Index Delete operator supports row mode execution only. |
| Blocking | The Clustered Index Delete operator is non-blocking. |
| Memory requirement | The Clustered Index Delete operator does not have any special memory requirement. |
| Order-preserving | The Clustered Index Delete operator is fully order-preserving. |
| Parallelism aware | The Clustered 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 Clustered Index Delete operator is not segment aware. |