What is the function of rebuilding indexes?

Question

I'm young in the IT world, and I'm learning daily. I just started working with SQL databases, and I have lots of basic questions.

One in particular, What is the function of rebuilding indexes? I have yet to find a good answer through Google.

Any feedback is appreciated.

Answer 1

Indexes are rebuilt to remove fragmentation. There a thousand and one articles and blog posts on the nature of index fragmentation but @BrentOzar recently posted a particularly concise explanation in Stop Worrying About SQL Server Fragmentation.

Let’s take a step back for a second and pretend that your database is a phone book organized by last name, first name.

As people move into your city, we have to add them to the phone book. Ideally, each page has some empty space, and we govern that with the fill factor. When SQL Server rebuilds indexes, it uses the fill factor to decide how much free space to leave on each page. If there’s not enough free space, SQL Server has to do some rearranging – but it can’t exactly shove a brand new page in the middle of the phone book. The book’s already bound. We’ll have to tack more blank pages onto the end.

Problem #1 – Internal Fragmentation: We’ve got a newly added page with hardly any stuff on it. Problem #2 – External Fragmentation: The phone book pages are out of order.

Brent's recent article offers an updated perspective on one of his previous series, Index Fragmentation Findings. The older article highlights statistics from much older studies on the damaging effects of fragmentation, the more recent makes a case for mitigating a large percentage of the performance downsides by ensuring your database is fully cached.

Ram is now so comically cheap that it is probably the cheapest, easiest, lowest risk solution to a heavily fragmented database. Especially if the nature of the database design is such that it will naturally become fragmented despite maintenance efforts.

Answer 2

The over simplified answer - Index rebuild is a way to remove index fragmentation. It drops the specified index and creates again removing fragmentation in the process. You may find this article useful