Maintaining Indexes and Statistics
Maintaining indexes and statistics is essential in
most database environments, including those that have frequent changes
to the data. These changes can cause tables and their indexes to become
fragmented and inefficient. These types of environments can also lead to
outdated statistics on indexes. Outdated statistics can cause the query
engine to make less-than-optimal choices when determining the best
access path to the data.
The maintenance of indexes and statistics is
facilitated through the use of three different tasks in the Maintenance
Plan Wizard: Reorganize Index, Rebuild Index, and Update Statistics.
Using the Reorganize Index task is equivalent to running the ALTER INDEX REORGANIZE
command. This task defragments and compacts clustered and nonclustered
indexes on tables and views. This helps improve index-scanning
performance and should improve overall response time. The operation is
always done online and is also equivalent to running the DBCC INDEXDEFRAG command.
Figure 7
shows the screen you use to define the Reorganize Index task. This
screen allows you select tables, views, or tables and views. You can
also select specific tables or views that you want to reorganize. The
Compact Large Objects option is equivalent to ALTER INDEX LOB_COMPACTION = ON. It causes data in large object (LOB) data types, such as image or text objects, to be compacted.
The Reorganize Index task moves the leaf-level pages
so that they match the logical ordering of the index. This behavior
improves performance, but it is not as extensive as the Rebuild Index
task, which is equivalent to the ALTER INDEX REBUILD command. It is also equivalent to the DBCC DBREINDEX
command. When the Rebuild Index task is executed, it rebuilds the
indexes from scratch. This rebuilding can achieve the best performance
results, but it also has the most impact on users of the database.
Figure 8 shows the options available for rebuilding an index with the Maintenance Plan Wizard.
The options for rebuilding are separated into two
sections: Free Space Options and Advanced Options. The Free Space
Options section pertains to the amount of free space left in the index
pages after the rebuild operation completes. This free space is defined
by the fill factor for the index. When the Reorganize Pages with the
Default Amount of Free Space option is used, the fill factor is reset to
the value used when the index was created. The other option, Change
Free Space per Page Percentage To, allows you to choose a new fill
factor value to be used for all indexes that have been selected for the
rebuild operation.
The following advanced Rebuild Index task options are available:
Sort Results in tempdb— This option is equivalent to the SORT_IN_TEMPDB option for the index. It causes tempdb
to be used to store intermediate results while rebuilding the index. If
this option is not used, these intermediate results are stored in the
database in which the index resides. Storing the results in tempdb can help prevent unnecessary growth of the user database in which the index is being rebuilt.
Keep Index Online While Reindexing— This option is equivalent to the ONLINE
option for the index. It allows users to access the underlying table
and associated indexes during the index rebuild operation. If this
option is not used, the index rebuild is on offline operation, and a
table lock is held on the table that is having its indexes rebuilt.
The maintenance of statistics can be just as
important as the maintenance of indexes on a table. Statistics contain
information about the distribution of data in tables and indexes and
provide valuable information to the SQL Server query engine. When the
statistics are outdated, the query engine may not make the best
decisions for getting the data.
Fortunately, there are database options that cause statistics to be automatically updated. The AUTO UPDATE STATISTICS and AUTO UPDATE STATISTICS ASYNCHRONOUSLY
options cause index statistics to be created automatically. However, in
some situations the automatic update of statistics does not happen
often enough, or the update happens at inopportune times and can cause
performance issues. You can address these situations by scheduling the
updating of statistics via a maintenance plan, using the Update
Statistics task.
Figure 9 shows the Maintenance Plan Wizard screen for setting the Update Statistics task options.
The top portion of the Define Update Statistics Task
screen is much like the option screens for maintaining indexes. You can
choose the type of objects (tables or views) on which you want to update
statistics, or you can focus on specific tables or views. The Update
options at the bottom of the screen identify the types of statistics to
be updated. If the All Existing Statistics option is selected,
statistics for both indexes and columns are updated. Statistics on
columns exist if the AUTO CREATE STATISTICS option has been set to ON
or the statistics were manually created. The other two update options
on the screen allow you to focus the update of statistics on columns
only or indexes only.
Scheduling a Maintenance Plan
One of the greatest features of a maintenance plan is
that you can schedule it. Scheduling takes manual work off your plate
and provides consistency that might be missed if the plan had to be run
manually. History is kept for each of the scheduled executions, which
provides an audit trail, and notifications can be tied to the scheduled
plans to allow a user to respond to failures or other results from the
plan.
A schedule can be created for an entire maintenance
plan or individual schedules can be created for each task in the plan.
The scheduling selection is available on the Select Plan Properties
screen, which is one of the first screens displayed while using the
Maintenance Plan Wizard (refer to Figure 1).
Choose the Separate Schedules for Each Task option to create a schedule
for each task. The default option is Single Schedule for the Entire
Plan or No Schedule. If you choose the option for separate schedules,
the Schedule Change button is enabled on the task definition screen and
this schedule is tied to that specific task.
Both scheduling options utilize the same scheduling
screen. The screen to set scheduling options, the Job Schedule
Properties dialog box shown in Figure 10,
appears when you click on the Schedule Change button. This screen
contains the same flexible scheduling features available in the SQL
Server Agent.
When a maintenance plan is saved, a scheduled job
with the same name as the maintenance plan is created. The job schedule
defined for the maintenance plan is applied to the scheduled job, and
the SQL Server Agent manages the execution of the job, based on the
schedule. Scheduling changes made to the maintenance plan are
automatically reflected in the scheduled job. In addition, if the name
of the maintenance plan is changed, the name of the scheduled job is
changed as well. If an attempt is made to delete the scheduled job
related to the maintenance plan, an error is returned, disallowing the
deletion.
Tip
Scheduling in the Maintenance Plan Wizard is limited
to one schedule per plan or task depending on which option you choose.
You can surpass this limitation by adding additional schedules to the
scheduled job associated with the maintenance plan. To do so, you simply
open the associated scheduled job located in the SQL Server Agent
node in SSMS and create the additional schedules. This capability is
handy when you want a varied execution, such as a weekly schedule
combined with daily executions of the same plan.
The scheduled job associated with a maintenance plan executes an SSIS package. Figure 11 shows an example of the scheduled job step for a SQL Server 2008 maintenance plan.
The utilization of SSIS in the execution of
maintenance plans was a significant change in SQL Server 2005. SSIS
provides added workflow capabilities and extends the feature set for
maintenance plans. The scheduled job step that executes an SSIS package
for the maintenance plan shows some of the options and flexibility of
SSIS, but the real power is in the maintenance plan editor and the
Business Intelligence Design Studio (BIDS) used to manage all SSIS
packages.
An integral part of a scheduled maintenance plan is
the notification and reporting capabilities. The Select Report Options
screen is displayed at the end of the Maintenance Plan Wizard (see Figure 12).
The option Write a Report to a Text File provides
details about the execution of each maintenance plan. This option should
be selected for most plans, and it provides excellent information for
researching past executions and diagnosing any maintenance plan
failures.
The E-mail Report option provides a means for
notifying a SQL Server operator when a task fails. You must have
Database Mail enabled to be able to use this option, and the operator
selected must have a valid email address to receive the notification.
You can also edit the job associated with the maintenance plan after it
has been created and set up notification there. The notification options
on the scheduled job are more extensive than those in the Maintenance
Plan Wizard.
Caution
If you have a maintenance plan generate a
report, you need to make sure you have a means for cleaning up the
files. The wizard does not create a plan that deletes the older report
files. You can address this situation by modifying the plan after the
wizard has created it and adding a Maintenance Cleanup task. This same
task can be used to delete old database backup files. The modification
of a maintenance plan and addition of the Maintenance Cleanup task are
discussed in the following section.
