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.
|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)
|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)
|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.