Steve Fenton

SQL maintenance plan optimization

I was investigating a number of maintenance plans that were taking serveral hours to complete and I noticed some optimizations that could be made to the plans without actually affecting the behaviour very much.

The maintenance plan after the fixes is:

  • Check Database Integrity
  • Maintenance Cleanup Task
  • Rebuild Index
  • Clean Up History
  • Back Up Database (Full)

You can trade some speed for disk by making the cleanup task run less often. You can also run the “Rebuild Index” step less frequently. However, this plan reduced run times by more than five times.

Setting Up the Plan

SQL -> Management -> Maintenance Plans -> Maintenance Plan Wizard

Maintenance Plan

Written by Steve Fenton on