Bad Index Maintenance Plan
Our team had been complained about some performance problem when trying to query some data from their application. When I was checking on our database servers, I saw this maintenance plan - scheduled every day at 12AM.
Just in case, if you guys can't see the picture on the left, this is the detail of this someone's bad index maintenance plan:
Check Database Integrity –> Shrink Database (all user database, shrink database when it grows beyond 100MB, and amount of free space to remain after shrink is 10%, Return freed space to operating system) –> Reorganize Index –> Rebuild Index –> Update Statistics (Update all existing statistics, Full scan)
Regardless to any performance problem, I don’t think this is a good maintenance plan.
Rebuild Index or Reorganize Index
Use [sys.dm_db_index_physical_stats] to detect fragmentation in all or specific index. you can reorganize index with fragmentation between 30% – 70% , more than that just rebuild it.
Update Statistics
Reorganize index will not update the statistic in any way whereas rebuild index will update the statistic with full scan. That’s why you can update statistics after you reorganize index, but don't update statistics after rebuild index or reorganize index before rebuild index.