question

askmlx121 avatar image
askmlx121 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
dbasql-agentrebuild
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.