Databases Reference
In-Depth Information
I'll return to in a moment), or by providing sorted order, like in queries with
GROUP
BY
,
DISTINCT
or
ORDER
BY
clauses.
Part of the Query Optimizer's job is to determine if an index can be used to evaluate a
predicate in a query. This is basically a comparison between an index key and a constant
or variable. In addition, the Query Optimizer needs to determine if the index covers the
query; that is, if the index contains all the columns required by the query (referred to
as a "covering index"). It needs to confirm this because, as you'll hopefully remember, a
non-clustered index usually contains only a subset of the columns of the table.
SQL Server can also consider using more than one index, and joining them to cover
all the columns required by the query (index intersection). If it's not possible to cover all
of the columns required by the query, it may need to access the base table, which could be
a clustered index or a heap, to obtain the remaining columns. This is called a bookmark
lookup operation (which could be a Key Lookup or a RID Lookup, as explained in Chapter
2). However, since a bookmark lookup requires random I/O, which is a very expensive
operation, its usage can be effective only for a relatively small number of records.
Also keep in mind that, although one or more indexes
can
be used, it does not mean
that they will finally be selected in an execution plan, as this is always a cost-based
decision. So, after creating an index, make sure you verify that the index is, in fact, used
in a plan (and, of course, that your query is performing better, which is probably the
primary reason why you are defining an index). An index that it is not being used by any
query will just take up valuable disk space, and may negatively impact the performance of
update operations without providing any benefit. It is also possible that an index, which
was
useful when it was originally created, is no longer used by any query. This could be
as a result of changes in the database, the data, or even the query itself. To help you avoid
this frustrating situation, the last section in this chapter will show you how you can
identify which indexes are no longer being used by any of your queries.
