Database Reference
In-Depth Information
From the preceding execution plan, you can see that the optimizer used the nonclustered index,
IX_PurchaseOrder_VendorID
, on column
VendorlD
and the new nonclustered index,
IxTEST
, on column
OrderlD
to
serve the query fully without hitting the storage location of the rest of the data. This index join operation avoided the
lookup and consequently decreased the number of logical reads from 10 to 4.
It is true that a covering index on columns
VendorlD
and
OrderlD (cl, c2)
could reduce the number of logical
reads further. But it may not always be possible to use covering indexes, since they can be wide and have their
associated overhead. In such cases, an index join can be a good alternative.
Summary
As demonstrated in this chapter, the lookup step associated with a nonclustered index can make data retrieval through
a nonclustered index very costly. The SQL Server optimizer takes this into account when generating an execution plan,
and if it finds the overhead cost of using a nonclustered index to be high, it discards the index and performs a table
scan (or a clustered index scan if the table is stored as a clustered index). Therefore, to improve the effectiveness of a
nonclustered index, it makes sense to analyze the cause of a lookup and consider whether you can avoid it completely
by adding fields to the index key or to the
INCLUDE
column (or index join) and creating a covering index.
Up to this point, you have concentrated on indexing techniques and presumed that the SQL Server optimizer
would be able to determine the effectiveness of an index for a query. In the next chapter, you will see the importance
of statistics in helping the optimizer determine the effectiveness of an index.
