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
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.