The IndexOptimize stored procedure incorporates
these concepts into its logic. This stored procedure
categorizes indexes into six groups based on
their level of fragmentation (high, medium, or low)
and whether LOB columns are present (LOB) or
not (NonLOB). The parameters that represent these
groups are as follows:
- @FragmentationHigh_LOB represents indexes
with a high fragmentation level and LOB columns.
- @FragmentationHigh_NonLOB represents
indexes with a high fragmentation level and no
LOB columns.
- @FragmentationMedium_LOB represents indexes
with a medium fragmentation level and LOB
columns.
- @FragmentationMedium_NonLOB represents
indexes with a medium fragmentation level and no
LOB columns.
- @FragmentationLow_LOB represents indexes with
a low fragmentation level and LOB columns.
- @FragmentationLow_NonLOB represents indexes
with a low fragmentation level and no LOB
columns.
IndexOptimize uses the avg_fragmentation_in_
percent column in the sys.dm_db_index_physical_
stats dynamic management view (DMV) to obtain
the percentage of fragmentation in each index. Using
the threshold limits you set in the @Fragmentation-
Level1 (lower threshold) and @FragmentationLevel2
(upper threshold) parameters, it places each index in
the appropriate group. Indexes with fragmentation
levels higher than the upper threshold go into one of
the high fragmentation groups. Indexes with fragmentation
levels at or between the two thresholds go into
one of the medium fragmentation groups. Indexes
with fragmentation levels under the lower threshold
go into one of the low fragmentation groups.
IndexOptimize can also place indexes in one of
the low fragmentation groups based on their page
count. Indexes under the size specified in the @Page-
CountLevel parameter go into one of the low fragmentation
groups.
For each group, you can select one of the following
actions:
- 'INDEX_REBUILD_ONLINE'—Tells the stored
procedure to rebuild the indexes online. (You need
to be running the Enterprise or Developer Edition
of SQL Server 2008 or SQL Server 2005 to use this
option.)
- 'INDEX_REBUILD_OFFLINE'—Tells the stored
procedure to rebuild the indexes offline.
- 'INDEX_REORGANIZE'—Tells the stored procedure
to reorganize the indexes.
- 'INDEX_REORGANIZE_STATISTICS_
UPDATE'—Tells the stored procedure reorganize
the indexes and update the statistics.
- 'STATISTICS_UPDATE'—Tells the stored procedure
to update the statistics.
- 'NOTHING'—Tells the stored procedure to do
nothing to the indexes.
So, for example, the EXECUTE statement in Listing
1 tells IndexOptimize to rebuild indexes that are
more than 30 percent fragmented, online if possible
(no LOBs). If these highly fragmented indexes have
LOBs, an offline rebuild is to be done. Indexes with a
fragmentation level between 5 percent and 30 percent
are to be reorganized and have their statistics updated.
Indexes that are less than 5 percent fragmented
or have fewer than 1,000 pages aren’t to be touched.
IndexOptimize uses T-SQL’s ALTER INDEX command
to rebuild and reorganize indexes.
How to Check Databases’
Integrity
The DatabaseIntegrityCheck stored procedure uses
T-SQL’s DBCC CHECKDB command to perform
integrity checks. Using this stored procedure instead
of the Database Maintenance Plan Wizard’s Check
Database Integrity Task might mean that you don’t
have to install the hotfix for bug 50001012. (The
Check Database Integrity Task can lose database context
under certain circumstances in SQL Server 2005
builds 3042 through 3158—see support.microsoft.com/kb/934458.)
The EXECUTE statement you use to run
DatabaseIntegrityCheck is simple. You just need to
specify the databases you want to check. For example,
the statement
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES'
checks the integrity of all user databases.
All Types of Information Are
Readily Available
The DatabaseBackup, IndexOptimize, and Database-
IntegrityCheck stored procedures have thorough logging
and error handling. The start and end time, command
text, and output are logged for each command
in the stored procedures. Additional information is
logged for the IndexOptimize’s ALTER INDEX command,
as Figure 1 shows. All command information is
immediately written to a log file. You can find information
about how the stored procedures handle errors in
MaintenanceSolution.sql’s documentation at http://ola.hallengren.com/Documentation.html. The documentation also includes information
about how to use each stored procedure as well as
answers to frequently asked questions.