Inside SQL Server Maintenance Plans

Profiling Optimization Maintenance Plan Options

In this step, I wanted to see happened under the covers when various Optimization options (see illustration below) from the Database Maintenance Wizard are traced with Profiler. As before, I will experiments with several different Optimization options, and run the resulting jobs manually in order to capture the traces.

Below are the results, formatted into a table for easy viewing.

Comments on this test run:

1. DBCC DBREINDEX uses free space as the inverse of the fill factor. So in the first row in the above table, free space = 0%, which is the same as a fill factor of 0. In second position we can see a value of 30% for free space, which is the same as a 70% fill factor.
   

2. If you choose DBREINDEX for a database, an UPDATE STATISTIC is done automatically
   

3. Even though the UPDATE STATISTIC command gives us the possibility to choose either a SAMPLE option of PERCENT or number of ROWS, the maintenance plan allows you only to specify PERCENT.
   
   

Profiling Other Maintenance Plan Options

I will not describe the database and log backup options of the Database Wizard because they are very straight-forward, and work like you would expect them to. However, I would like you to discuss some other maintenance plan options which are accessible only from xp_sqlmaint utility and not from the Maintenance Plan Wizard:

• HtmlRpt "e:\mssql7\LOG\ak_test.html": Creates an HTML version of the database maintenance report.
  

• DelHtmlRpt <time period>: Sets the retention cycle for HTML files, e.g. 3 WEEKS.
  

• CkCat: Runs DBCC CHECKCATALOG.
  

• CkAl, CkAlNoIdx: Runs DBCC NEWALLOC.
  

• CkTxtAl: Runs DBCC TEXTALL.

Comments on These Commands:

1. Be careful with the syntax of the above switches. They must be exactly as I listed above. For example if you type Htmlrpt (instead of HtmlRpt) the job will fail.
   

2. Don’t use NEWALLOC and TEXTALL. They are included for backward compatibility. CHECKALLOC replaced NEWALLOC and CHECKTABLE TEXTALL. It’s better to use CHECKDB, which performs CHECKTABLE for each table in a database to check the integrity of the data, and CHECKALLOC to check allocation of all pages.