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.
