>>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.
You don't need to reindex everything, the script checks fragmentation level and only re-builds what needs to be re-build.
If it's not broken, fix it until it is.
My Blog