>You need to run maintenance of indexes periodically. Most people recommend Olla Hallogreen (?) scripts, but I recommend Michelle Ufford scripts for indexes de-fragmentation. I posted link here before.
If I would need to reindex everything, I already have everything in place for that. I just do not want to do it if it is not necessary. I have been running Web sites for years without having to reindex on a regular basis or not at all and the sites still answer under 0.2 second and I am dealing with tables having more than two millions records.
On another application, only a few queries takes like 3 to 4 seconds. They are executed against 4 to 8 million records table. But, those are very complex queries. So, in overall, so far so good.