asked
If we change Agent Job routine in DBA Task? Pros or Cons?
In My production Server, There is Agent Job routine **Backup Database Task-->Check Database Integrity task-->Rebuild index task-->Shrink Database Task-->Update Statistics Task.** Each Task runs in some time interval. My Question is If I change this Agent Job routine like below Backup Database Task-->Check Database Integrity task-->Shrink Database Task-->**Update Statistics Task-->Rebuild index task**--> **Any issues/Performance issue would occur or not?** ![alt text][1] Note: My DBA has recently changed this Statistics update task runs before instead of Index rebuild task [1]: /storage/temp/923-job+agent+schedule.gif
First, why are you shrinking the database in an automated fashion? That's HIGHLY problematic. You're introducing massive physical fragmentation in your database files by doing that. I would absolutely caution against it in the strongest terms. As to the order of operations, statistics or index rebuild, Well, I'd suggest that if you're rebuilding all your indexes, you don't need to do a statistics update at all. Rebuilding indexes causes statistics to get updated using a full scan. So your indexes will have the best possible statistics after the index rebuild. If you then update those statistics, they'll get sampled, actually making the statistics worse. The exception to this are any statistics that are not on indexes. They would need to get updated independently in some fashion. If I had to pick, I'd go with the statistics update followed by the index rebuild. One other point, I'd also suggest breaking these up. Make the DBCC and backup a separate task. Further, put the DBCC before the backup, so you can check the database before you back it up. Then you can schedule these separately too.
14 People are following this question.
