Microsoft SQL Server 2012 Bible

Summary

To intelligently create indexes you need to thoroughly understand not only the technologies — 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 pragmatically using that knowledge to leverage performance from the system. ...