1039
Chapter 45: Indexing Strategies
45
Summary
To intelligently create indexes you need to thoroughly understand not only the technolo-
gies — the Query Optimizer, index pages, and indexing options — but also both your
schema and your queries. Indexing is essentially a bridge from the query to the data.
Although indexes can’t fully overcome a poor schema or poorly written queries, a database
without good indexing is sure to perform poorly.To highlight the key ideas about indexing:■ (^) Clustered indexes store all the data of the base table, logically organized by the
index keys.
■ (^) Nonclustered indexes are subsets of data with their own keys and optionally
included columns.
■ (^) A nonclustered index that completely solves the query without having to jump over
to the clustered index (using a bookmark lookup) is referred to as a covering index.
■ (^) Bookmark lookups are the tipping point of indexing - where scans vs seeks are
decided. For the queries that consume the most CPU duration, avoid them with
clustered indexes or covering indexes. For the other queries, bookmark lookups are
the preferable method to reduce the number of indexes.
■ (^) Filtered nonclustered indexes include only a small subset of rows, are faster to
maintain, and can make perfect covering indexes.
■ (^) Indexed views are custom indexes that actually materialize data and can pull from
multiple base tables or pre-aggregate data.
The next chapter continues the theme of understanding SQL Server internals and pragmati-
cally using that knowledge to leverage performance from the system.
c45.indd 1039c45.indd 1039 7/31/2012 10:16:45 AM7/31/2012 10:16:45 AM
http://www.it-ebooks.info