Databases Reference
In-Depth Information
Per-row and per-index plans
An important operation performed by updates is the modifying and updating of existing
non-clustered indexes, which is done by using per-row or per-index maintenance plans
(also called narrow and wide plans, respectively). In a per-row maintenance plan, the
updates to the base table and the existing indexes are performed by a single operator, one
row at a time. On the other hand, in a per-index maintenance plan, the base table and
each non-clustered index are updated in separated operations.
Except for a few cases where per-index plans are mandatory, the Query Optimizer can
choose between a per-row and per-index plan based on performance reasons, and on an
index-by-index basis. Although factors like the structure and size of the table, as well as
the other operations performed by the
UPDATE
statement, are all considered, choosing
between per-index and per-row plans will mostly depend on the number of records
being updated. The Query Optimizer is more likely to choose a per-row plan when a
small number of records are being updated, and a per-index plan when the number of
records to be updated increases, as this choice scales better. A drawback with the per-row
approach is that the storage engine updates the non-clustered index rows using the
clustered index key order, which is not efficient when a large number of records need to
be updated.
The query in Listing 6-4 will create a per-row plan, which is shown in Figure 6-3 (two
additional queries may be shown on the plan due to the execution of an existing trigger).
Note
The following two queries delete data from the AdventureWorks database, so perhaps you should request
an estimated plan if you don't want the records to be deleted. Alternatively, you could perform a database
backup before running these queries, so that you will be able to restore the database later.
