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.
| Which Optimization Settings Were Used | Profiler Trace Results | Xp_Sqlmaint Syntax |
| Reorganize data and index pages, reorganize pages with the original amount of free space | dbcc dbreindex(dbo.Table1,”, 0, sorted_data_reorg) (for each table in database) |
-RebldIdx 100 |
| Reorganize data and index pages, change free space per page percentage to 30% | dbcc dbreindex(dbo.Table1′, ”, 70, sorted_data_reorg) (for each table in database) |
-RebldIdx 30 |
| Update the statistics used by the query optimizer, percentage of database to sample 30% | UPDATE STATISTICS dbo.table1 WITH all, SAMPLE 30 PERCENT (for each table in database) | -UpdOptiStats 30 |
| Remove unused space from database files | DBCC shrinkdatabase(test, 10, TRUNCATEONLY ) | -RmUnusedSpace 2048 10 |
Comments on this test run:
-
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.
-
If you choose DBREINDEX for a database, an UPDATE STATISTIC is done automatically
-
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:mssql7LOGak_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:
-
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.
-
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.