In the most recent installments of our series dedicated to SQL Server 2005 Integration Services, we started exploring database maintenance functionality. Our interest in this subject (within the context of our series) results from the fact that SSIS Designer of Business Intelligence Development Studio includes a number of Control Flow tasks that belong to this category. However, as we have demonstrated, an equivalent set of features is also available through Maintenance Plan Wizard and Maintenance Plan Designer components of SQL Server Management Studio. In this article, we will focus on the second one of these utilities, describing its characteristics in more detail.
Maintenance Plan Designer gives you access to a number of tasks in the manner similar to the one implemented in SQL Server Business Intelligence Development Studio. Within its Toolbox, you will find a total of 11 tasks, which allow you to perform a variety of database management activities (note though that SQL Server 2005-based maintenance plans only run against databases at compatibility level 80 or higher). We will provide a brief overview of each of them, focusing on their recent improvements (introduced in SQL Server 2005 SP2) and intricacies that you should be aware of (for general information regarding their features you can refer to individual articles of the "SQL 2005 Maintenance Wizard" series published on the Database Journal Web site):
Back Up Database task allows you to perform backup of one or more databases using Full, Differential, and Transaction Log options (which, incidentally, are listed as three separate entries on the Select Maintenance Task page of Maintenance Plan wizard). If you installed SQL Server 2005 SP2, you will be able to specify an expiration date of a backup set (either as the number of days following its creation or a specific date, which corresponds to RETAINDAYS and EXPIREDATE T-SQL parameters of the BACKUP DATABASE statement). Unfortunately, this innovation appears to be of a limited practical use at this point (at least as long as your intention was to rely exclusively on predefined Maintenance Plans tasks) since there seems to be no graphical interface for setting the NOSKIP option that verifies the expiration status of backup sets. (For more information regarding interdependencies between SKIP/NOSKIP and INIT/NOINIT T-SQL arguments, refer to SQL Server 2005 Books Online). Another improvement (slightly more useful) is automatic restriction of available backup types for a target database based on its recovery model.
Maintenance Cleanup task is frequently paired up with the Back Up Database task in order to delete outdated backups files and maintenance plan reports (based on file extensions that you specify). This task was conspicuously missing from pre-SP2 versions of SQL Server 2005 Maintenance Plan Wizard (especially since it was available in its SQL Server 2000 rendition). However, the new implementation, while filling this void, introduced a number of unexpected issues. Its initial release (which had been posted on the Microsoft Web site until March 05, 2007) was not backwards compatible and in many cases resulted in deletion of backup files sooner or later than intended. The confusion apparently was caused by newly introduced hourly intervals, which were incorrectly interpreted when modified and processed by a mix of pre- and post-SP2 utilities (for more details regarding this issue and its resolution, refer to Microsoft Knowledge Base article 933508). In addition, while SQL Server 2005 SP1 provided the ability to delete aged maintenance plan files from subfolders of a target folder (this feature is currently configurable with the "Include first-level subfolders" checkbox in the Maintenance Cleanup Task editor dialog box), it failed to address the inability to delete files based on their extension. (Following SP2 installation, the task can remove backup files, but does not purge reports). Until this flaw is fixed, you will need to resort to workarounds (such as, incorporating the master.dbo.xp_delete_file extended stored procedure with an appropriate set of parameters into Execute T-SQL Statement Task).
History Cleanup Task serves a role similar to the Maintenance Cleanup task, although instead of dealing with files created by maintenance plans, it removes outdated entries from a number of tables in the msdb database (backupfile, backupfilegroup, backupmediafamily, backupmediaset, backupset, restorefile, restorefilegroup, and restorehistory). Note that these entries might be generated not only by maintenance plans, but can also appear as the result of performing standard backups and restores, or executing SQL Server Agent jobs. The scope of cleanup is determined by the content of "Remove historical data older than" property that, starting with SQL Server 2005 SP2, can have its value expressed in hours, in addition to days, weeks, months, and years (which have been available earlier).
Check Database Integrity task is designed as a graphical interface to DBCC CHECKDB, which, in turn, incorporates features encompassed by DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG. By leveraging these DBCC procedures, the task performs a variety of tests, such as verifying allocation of all database pages, confirming structural integrity of tables and indexed views, and checking consistency between system metadata tables (any issues discovered through these actions would need to be subsequently addressed through proper administrative actions). Note that, depending on the version and build number of your SQL Server 2005 installations (refer to the Knowledge Base article 321185 to find out how to determine their values) and the way you structure your maintenance plans (in particular, when verifying integrity of multiple databases within the same task), you might be running into problems outlined in the Microsoft Knowledge Base articles 934459 or 934458. (Both detail the actual issues and point to a patch posted on the Microsoft Download Center that fixes them).
Rebuild Index Task is used to improve performance of operations that involve indexes by recreating them a specific amount of free space on the leaf-level pages according to the value of its Fill Factor parameter (compacting and reordering them in the process), which decreases index fragmentation and lowers the amount of page splits during subsequent data inserts/modifications. SQL Server 2005 Enterprise Edition is capable of performing this operation online, without restricting access to the underlying table, or its clustered and nonclustered indexes (to take advantage of this feature, turn on the "Keep index online while reindexing" advanced option in the task Editor dialog box). There are two other advanced options that, while present in pre-Service Pack 2 instances, no longer appear following its installation. The first, labeled "Pad index," controls the amount of free space in intermediate-level pages of the index that is being rebuilt (based on the value of the fill factor parameter). The second one, called "Ignore duplicate keys," dictates behavior triggered by an attempt to add a duplicate into a table with unique index, as part of a multi-row transaction. With this option enabled, such a scenario will generate a warning but permit non-violating rows to be inserted; keeping it disabled (which is the default) will result in an error and rollback of the entire INSERT transaction. Apparently, Microsoft decided to remove these two settings due to a design flaw, which resulted in applying their values to all indexes across target objects, regardless on their previous values (i.e. following the task execution, all of the target indexes were ending up with the same PAD_INDEX and IGNORE_DUP_KEY settings). Since this frequently was not the intended outcome, the new default simply reapplies original index options during its recreation (hence, there is no need to include it in the list of configurable options).
Reorganize Index Task (based on the ALTER INDEX REORGANIZE T-SQL statement) provides a less intrusive (and automatically invoked in online mode), but also less powerful alternative to rebuilding an index. It allows you to perform online reordering and compacting index leaf-level pages (without reapplying their fill factor parameter), leading to their defragmentation, which, in turn, results in improved performance. In general, Microsoft guidelines recommend its use in situations when the index fragmentation level (which can be determined by examining the outcome of the sys.dm_db_index_physical_stats function) is below 30% (you should consider rebuilding an index otherwise).
Shrink Database Task (which leverages DBCC SHRINKDATABASE statement) is typically used to reduce the size of a files containing a target database and its logs (e.g. following truncating or dropping a large table), which is accomplished by compacting its content and lowering the amount of extra free space they contain (based on the value of the "Amount of free space to remain after shrink" task property, representing the percentage of free space that is left after the task completes). Its effectiveness depends on having a sufficient amount of free space in the database (to establish its value, use sp_spaceused stored procedure), as well as such factors as minimum database size. Note that you will not see any changes in file sizes if you selected the "Retain freed space in database files" option (corresponding to the NOTRUNCATE argument of DBCC SHRINKDATABASE), which only rearranges allocated pages of a database data file (but not logs) to make them contiguous (by moving their content to unallocated pages in the beginning of the file) but without returning the resulting free space at the end of the file to the operating system.
Update Statistics Task recalculates information that represents data distribution in tables and indexes, which is then used by query optimizer to determine the most efficient approach when processing T-SQL statements. The recalculation process can be based either on a full scan (which might take a considerable amount of time with larger objects but provides the best accuracy) or by analyzing a sample of arbitrary size (which saves processing time necessary to generate statistical data but at the risk of potential inaccuracies). Starting with SQL Server 2005 SP2, an updated task editor interface gives you the ability to specify scan type (you can choose between full scan and a sample-based one, with its size determined by either a percentage of the entire data set or a number of rows).
Execute T-SQL Statement Task provides the ability to launch custom T-SQL statements, which functionality is not covered by any of existing maintenance plan tasks. This allows you to supplement the outcome of Maintenance Plan wizard (which, by the way, does not include it on its Select Maintenance Task page).
Notify Operator Task, which leverages Database Mail functionality (for more information on this subject, refer to Books Online), is limited strictly to e-mail notifications and requires an existing SQL Server Agent operator entry with its e-mail address property defined. As with the Execute T-SQL Statement task, this feature is not available directly from the Maintenance Plan wizard.
Execute SQL Server Agent Job task allows you to incorporate existing jobs into maintenance plan workflow (you can simply pick the ones you are interested in directly from the task editor dialog box).
In the next article of our series, we will take a closer look at Maintenance Plan tasks available from within SSIS Designer of Business Intelligence Development Studio and compare them with equivalent functionality implemented in SQL Server Management Studio.