I will be planning to do the Rebuild/ Reorg Indexes as my client never had done this and never run update statics. But my problem is that we have replication DB and it's runs very fw minutes as we are subscriber. If I do the Rebuild Index and it will lock the table during maintenance. We are running some evening and nightly jobs too. Replication DB is Big around 200 GB, will be a problem if i do the Index maintenance during week end and publication running? I have already ran the script to identify the Index fragmentation and know which index needs to be Rebuild or Reorg.
I would suggest only running reorg. If you aren't experiencing a performance or space issue, then rebuild is basically a waste of downtime/time. Due to the huge headache that rebuild was causing, we just disabled the job and haven't needed to run it in many months. We disabled it at the advice of a very senior MS engineer. We do have Enterprise edition and were using ONLINE=ON, but it still was causing way too many production issues and really no benefit.
Thanks Russell. Thanks Tara. I am completely agreed with you but I believe my client is not running any Index Rebuild or ReOrg from long time and also they never run Update Stats so I thought let me run once a Rebuild script and Update the stat as we are experiencing long wait from User perspective and it's slowing whole process and normally it's happens when Replication runs or any batch job runs. Please advice, appreciate your help!
I would only update stats. Setup a nightly job to do it. Then go back and see if it resolved any issues. Rebuild/reorg are likely not going to help with any performance issues. If you have performance issues after update stats, you could try rebuild/reorg, however you'll likely find it didn't do anything and then will need to analyze code/indexes/execution plans/PerfMon/etc.