Microsoft® SQL Server® 2008 Bible

Chapter 64. Indexing Strategies

IN THIS CHAPTER

• Indexing for improving performance

• Interpreting query execution plans

• A database strategy for improving performance

My son Dave and I love to watch "MythBusters." Even if we know Adam and Jamie aren't going to get the pig's head to blow up, it's a blast to see them try.

If there's any aspect of SQL Server populated with misconceptions (or shall I say, "mythconceptions"), it's indexing—and that's unfortunate, because sound indexing isn't all that complicated.

My Smart Database Design Seminar is based on the notion (described in Chapter 2) that an elegant physical schema lends itself to writing great set-based queries that respond well to indexing. It's the theory I use when I design a database and when I go on a performance-tuning and optimization consulting job. One aspect of Smart Database Design is that no layer can overcome deficiencies in lower layers, i.e., no index will solve an inappropriate cursor and a sorry database schema.

Nulltheless, indexes are at the heart of SQL Server performance; they are the bridge from the question to the data—from the query to the schema.

Indexes are so critical to SQL Server performance that I've spent two months longer than my writing schedule allowed to explain how to think about indexes, work through the twelve query paths, and present a solid indexing strategy.

Zen and the Art of Indexing

Right up front, here's my secret to designing effective indexes: When I'm indexing, in my mind's eye I don't see ...