Microsoft® SQL Server® 2012 Bible

1005

CHAPTER

45

Indexing Strategies

IN THIS CHAPTER

Indexing for Improving Performance

Interpreting Query Execution Plans

Improving Database Strategy Performance

O

ver the years I’ve amassed a relatively large DVD collection. It’s not a huge collection, but

it can become somewhat painful when I have a movie I really want to watch and I need

to fi nd it among my other movies. What I should do is maintain my movie collection in a

sorted order. This would make searching for a movie far less painful and much quicker. However,

this approach is not without its own overhead. First, I’d have to perform one large sort of the

movies (based on the movie title) and make sure I have them stored on my shelves based on a

physical sorted order. This would be a relatively slow operation because I’d have to touch every

DVD and move it around to make sure it is where it needs to be. There are, of course, numerous

sorting algorithms I could use to perform this operation — but that is a story better discussed in

a different book.

After I’ve performed my initial build of my sorted DVD collection, I then must maintain my list.

Anytime I buy a new movie, I must make sure it is placed in the correct spot in the list. I must

also make sure to remember to always insert movies that I’ve taken out into the proper spot on my

shelf. This could be more overhead than I want to maintain in the long run, but it sure would make

searching for a DVD when I want to watch it a lot easier.

This is similar to how indexes work inside a database engine. Indexes make searching and sorting

so much faster because the data is maintained in a sorted order. Like in the DVD example, there is

also overhead involved in maintaining the data in sorted order. Indexes are so absolutely crucial for

database performance that a single missing or incorrectly defi ned index can bring a high-throughput

system to its knees.

This chapter presents some things to consider for creating proper indexes inside SQL Server

along with some common index operations that you should be aware of when performance

tuning.

c45.indd 1005c45.indd 1005 7/31/2012 10:16:35 AM7/31/2012 10:16:35 AM

http://www.it-ebooks.info