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.

When should I rebuild the indexes in my relational database (e.g. SQL Server)?

Is there a case for rebuilding indexes on a regular basis?

share|improve this question

4 Answers 4

up vote 13 down vote accepted

At the risk of being way too general in my answer, I will say that you should run an index maintenance process regularly. However, your index maintenance process should only rebuild/reorganize the indexes that specifically require it.

This presents the question: when does an index require to be rebuilt or reorganized? Rolando touched on this nicely. Again, I risk being extremely broad. An index requires maintenance when the fragmentation level adversely affects performance. This level of fragmentation could vary based on the size and composition of the index.

Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance. If an index contains less than 100 pages, I will perform no maintenance. If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics. If an index is over 30% fragmented, I will REBUILD the index (no UPDATE STATISTICS necessary, as this is taken care of by the REBUILD).

This answer is really just a long way to say: Yes, you should do routine index maintenance, but only on the indexes that need it.

Hope this helps,

Matt

share|improve this answer
    
This is more or less what we currently do at our company. Thank you for the concrete recommendations. –  Nick Chammas Aug 4 '11 at 1:28
2  
Be careful if you don't update stats on tables where you rebuild the index. Rebuilds only update the index driven stats. Stats aren't updated on stats which are build on columns which aren't indexed. –  mrdenny Aug 4 '11 at 18:55
    
Great point, and one that I am aware of, but neglected to mention. I typically handle updating column statistics in a separate process. –  Matt M Aug 5 '11 at 0:52

When should I rebuild the indexes in my relational database (e.g. SQL Server)?

You should rebuild indexes when they become highly fragmented by special events. For example, you perform a large, bulk load of data into an indexed table.

Is there a case for rebuilding indexes on a regular basis?

So what if your indexes are becoming fragmented on a regular basis due to regular activity? Should you schedule regular rebuilds? How often should they run?

Tom Kyte, in this classic Ask Tom thread, recommends:

The time lag between index rebuilds should be approximately FOREVER.

...

Don't know how to say it better -- the index wants to be big and fat with extra space. It is on a column you update -- moving the index entry from place to place in the index. One day the row has a code of "A", the next day the code is "G", then "Z" then "H" and so on. So the index entry for the row moves from place to place in the index. As it does so, it needs space -- will, if the space isn't there, we split the block into two -- and make space. Now the index is getting fat. Over time the index is 2-3x the size it was when you started and is "half or more empty" But that is OK since you move rows around. Now when we move the rows around, we no longer have to split blocks to make room -- the room is already available.

Then you come along and rebuild or drop and recreate the index (which have the same effects -- just the rebuild is "safer" -- doesn't stand a chance of losing the index and can be faster as the index can be rebuilt by scanning the existing index instead of scanning the table and sorting and building a fresh index). Now, all of that nice space is gone. We start the process of splitting the blocks all over again -- getting us right back to where we started.

You saved no space.

The index is right back the way it was.

You would just be wasting your time to rebuild it again causing this vicious cycle to repeat itself.

The logic here is sound, but it is biased against a read-heavy load profile.

A "fat" index (i.e. one with lots of gaps) does indeed keep a good amount of room for new and moved rows, thus reducing page splits and keeping your writes speedy. However, when you read from that fat index you'll have to read more pages to get the same data because you're now sifting through more empty space. This slows your reads down.

So, in read-heavy databases you want to regularly rebuild or reorganize your indexes. (How often and under what conditions? Matt M already has a concrete answer to this question.) In databases that experience roughly equivalent read and write activity, or in databases that are write-heavy, you are likely harming your database's performance by rebuilding indexes regularly.

share|improve this answer
3  
+1 - excellent answer. In your question you mention SQL Server and I do wonder if there is something fundamentally different about that platform that makes it different to Oracle, as this question is one that tends to polarise the two sets of DBAs –  Jack Douglas Aug 4 '11 at 15:56
    
I would love to know that as well. Is it just cultural? I wonder if I should remove the reference to SQL Server in my original question to attract answers for the other platforms. –  Nick Chammas Aug 4 '11 at 19:30

There will be fragmentation within a table and its indexes when the table experiences a high volume of INSERTs, UPDATEs, and DELETEs. Given that indexes are based on BTREEs, there will be a lot of half full BTREE pages.

Since balanced binary trees (worst case BTREE) usually requires node rotation 45% of the time to maintain a discreet height, I would expect BTREEs to have the shuffling of index entries and thus some fragmentation.

Having BTREE pages as full as possible reduces disk I/O (latency).

Another important factor in deciding to rebuild indexes is the Index Statistics. They are needed for generating EXPLAIN plans through the Query Optimizer. Depending on the RDBMS, those indexes are either computed on-the-fly or stored in repositories as some form of histogram. Oracle features histograms to suggest querying patterns for EXPLAIN PLAN generation (I am not an Oracle DBA, so Oracle Gurus Like Gauis and Leigh Riffel PLEASE CHIME IN YOUR OWN ANSWER on Oracle Histograms). MySQL's InnoDB storage engine computes index stats on-the-fly via diving into the index pages. On the flipside of MySQL, the MyISAM storage engine has static statistics that can get old pretty fast in a heavy CRUD environment. Doing ANALYZE TABLE recomputes index stats but disk fragmentation within the index can slightly nullify the benefits of computing statistics.

In summary, you should periodically rebuild indexes and recomute fresh index statistics.

share|improve this answer
4  
In Oracle refreshing the index statistics does not require rebuilding the index, therefore it isn't a determining factor. I agree with Nicks quote from Tom Kyte, rebuilding an index in Oracle is "wasting your time". –  Leigh Riffel Sep 16 '11 at 21:59
    
@Leigh - Thank you for chiming in !!! –  RolandoMySQLDBA Sep 16 '11 at 22:11

Most people rebuild them on a regular basis so that they never get to fragmented. When you need to rebuild them is based on how quickly they get fragmented. Some indexes will need to be rebuilt often, others basically never. Check out the script the SQLFool put together that handles a lot of figuring this stuff out for you.

share|improve this answer
    
+1 for that link, I like the rebuild/reorganize logic. Good stuff. –  SqlSandwiches Aug 3 '11 at 20:24

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.