SQL Maintenance Plans vs Jobs

Caution Using SQL Management Studio Maintenance Plan Wizard

Recently, I talked about setting up maintenance plans using the SQL Management Studio.  Recently, I have stopped using the Maintenance Plan Wizard, and have only set up the SQL Agent Jobs manually.

The reason is, that I have had a couple of instances where the jobs were not actually being performed with the Maintenance Plans.  However, the plan history indicated that the steps had been performed.  In one case, the "rebuild indexes" and "update statistics" were setup in the Maintenance Plan on a large database.  The history showed that the steps had been run, and in fact completed in about seven minutes. 

I had reason to suspect that the database had, in fact, not been re-indexed.  I rebuilt the indexes and updated statistics manually.  It took nearly an hour to go through, which was closer to the time I expected it to take.  The database performance increased dramatically, also, further confirming that the Maintenance Plan had not actually been performing these steps.

Interestingly, the rest of the Maintenance Plan appears to have been functioning.  The database backup was being performed, for example.

There is no reason for me to believe that these steps are not being executed via the Maintenance Plan for other machines, including several others used by the same company.  These machines are, for all practical purposes, identical.  

Consequently, I have decided to use the manually configured jobs, and not the Maintenance Plan Wizard, on all future scheduled jobs.  You may want to adjust you Maintenance Plan procedures to avoid the Wizard.

Dave.

Leave a Reply