When should I rebuild the indexes in my relational database (SQL Server)?
Is there a case for rebuilding indexes on a regular basis?
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
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.
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.
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?
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.
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.
When the index fragmentation percentage is more than 30%.
There is no such case, but in general, doing Index Maintenance once in a week, over the weekend is the best practice to keep the environment stable.
I would recommend using maintenance scripts from Ola Hallengren (best maintenance scripts), customize the scripts based on your environment and schedule them to run over the weekend.
Note: Please don't forget to update stats after rebuilding indexes, because rebuilding indexes doesn't update all the statistics.