SQL Server Performance Tuning : Per-Row and Per-Index Plans

One key operation done during a delete operation on a table is the updation of clustered and non-clustered indexes. SQL Server performs this using the per-row or per-index maintenance plans. In a per-row scenario, SQL Server first updates the Clustered Index followed by the non-clustered indexes using a single operator. The per-Index maintenance plan however has different operators performing the update operations on clustered and non-clustered indexes.

The query optimizer is likely to choose a per-row plan when no of records to be deleted are low in number. If you closely look in a per-row plan, SQL Server uses the Clustered Index Key to fetch the keys to be updated on the non-clustered indexes.

During operations involving large deletes, we would usually see SQL Optimizer choosing a per-Index Plan. For non-clustered indexes, Table Spools are created including attributes which are part of the key and sorted based on the respective index.

For the purpose of this demonstration, I shall create a table and populate it like so,

I create few non-clustered indexes on the above table

Now, lets go execute the delete statements. In our scenario, Query1 shall delete a single row whereas Query2 will delete large number of rows.

If we have the execution plan turned on, we shall observe similar to below. For Query1, we shall see that the same operator performs the updation operation on all indexes.

For Query2, we can observe that SQL Server created separate spools for all non-clustered indexes which were again sorted based on how the index keys are arranged.

There is an undocumented trace flag 8790 which may be invoked to force a per-index plan.You can validate that when no of records to be deleted are low, using this trace flag in fact deteriorates the performance. It is important to note that both maintenance plans may be used in the same query.