Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.