The maintenance actions outlined in this
section are directly associated with the corresponding analysis
activities from the previous section. We'll begin with dropping unused
or duplicated indexes before looking at removing index fragmentation.
1. Dropping and disabling indexes
In the previous section, we covered the use of
the sys.dm_db_index_usage_stats DMV along with a number of system tables
to identify indexes that are not used, used rarely, or duplicated. Once
identified, the indexes are candidates for removal. The question is,
how should they be removed? An index can be easily dropped using the DROP INDEX command as per this example:
DROP INDEX tablename.indexname
If the index was dropped as a result of being
identified as a duplicate or suspected as being of low value, then it's
possible that certain unexpected errors or performance problems
eventuate from the index being dropped. For example, a query may still
be referencing the index with an index hint, or a rare (but important)
report is executed that relies on the (now missing) index. In such
cases, if the definition of the index was not saved, that is, the index
type, columns, and column order, then it may take some time to restore
the index to its previous state.
In SQL Server 2005, the ALTER INDEX command was enhanced with a DISABLE option. Disabling an index has the following effects:
If nonclustered, the data is removed from the index but the index metadata definition remains.
User access to the index is not permitted.
The query optimizer will not consider the index.
If a clustered index is disabled, access to the table is prevented until the index is re-enabled via either the REBUILD or the CREATE WITH DROP_EXISTING command, both of which are covered shortly.
When it comes to removing a duplicated or
low-value index, disabling the index allows its definition to be
retained should the index need to be reinstated, without the maintenance
overhead of keeping it. An index can be disabled using the ALTER INDEX command as follows:
|
When you use the index REBUILD option
(covered shortly), additional temporary disk space is required for
storing the old and new copies of the index. One of the advantages of
disabling an index before rebuilding it is the reduction in disk space
required for the rebuild operation. The estimated overhead of rebuilding
a disabled index is approximately 20 percent on top of the index size.
The disadvantage of disabling an index is that it's unavailable for use
until the rebuild is complete. This technique may be appropriate if disk
space is limited and the temporary unavailability of the index is
permitted.
|
-- Disable an index
ALTER INDEX ixSalesOrderHeader_PurchaseOrderNumber
ON Sales.SalesOrderHeader
DISABLE
GO
The other major use for disabling indexes is during data warehouse loads. The classic load process is drop indexes, load data, reinstate indexes.
The problem with this approach is that the definition of the indexes
needs to be known when adding them back, complicating the process
somewhat, particularly when the table or index definition changes and
the change is not added to the drop/re-create process. In such cases,
the index creation may fail or add back an old, poorly performing
version of the index. By disabling an index rather than dropping it, the
index definition is not required when re-enabling it, simplifying the
re-creation process.
To re-enable a disabled index, use the ALTER INDEX REBUILD or CREATE INDEX WITH DROP EXISTING command, which we'll cover shortly.
2. Removing fragmentation
In this
section, we'll cover a number of different techniques for maintaining an
index and the implications these have on fragmentation, user access,
statistics, and transaction log usage.
ALTER INDEX REORGANIZE
The ALTER INDEX REORGANIZE option is the replacement for DBCC INDEXDEFRAG
used in earlier SQL Server versions. Reorganizing an index removes
fragmentation by reordering the leaf level of clustered and nonclustered
indexes by aligning the physical order with the logical order. An
example follows:
-- Reorganize (Defragment) an index
ALTER INDEX IX_SalesOrderHeader_SalesPersonID
ON Sales.SalesOrderHeader
REORGANIZE
The REORGANIZE method is ideal as a
low-impact, online method for removing light to medium levels of
fragmentation from an index. In most cases, an index with a 5-
to30-percent fragmentation level (as reported by the
avg_fragmentation_in_percent column in the sys.dm_index_physical_stats function) can be defragmented using this method, without any major user impact.
On the downside, REORGANIZE will not
remove fragmentation from the nonleaf (intermediate) levels, nor will it
update statistics. For that, we need a more thorough approach,
available through ALTER INDEX REBUILD.
ALTER INDEX REBUILD
ALTER INDEX REBUILD is the replacement for DBCC DBREINDEX
used in earlier versions of SQL Server. This command essentially drops
and re-creates the index, with a number of important differences.
First, the REBUILD method reads and
rebuilds every page, and consequently, its statistics, are updated in full. Second, all levels of the index are rebuilt, meaning a greater performance boost and disk space reduction compared to the REORGANIZE method. An example REBUILD command follows:
-- Rebuild an index (Online mode)
ALTER INDEX ALL
ON Sales.SalesOrderHeader
REBUILD WITH (ONLINE = ON)
You'll note a few differences (and similarities) between this command and REORGANIZE. First, it uses the same ALTER INDEX command, but in this case ALL is used in place of a specific index in the earlier example. ALL is supported for both REBUILD and REORGANIZE as a means to operate on all of the specified table indexes rather than a specific index.
The other option specified in the above example is ONLINE = ON.
Available in the Enterprise edition of SQL Server, this option rebuilds
the index in a manner that allows continued user access to the index
during the rebuild operation. Thus, it's ideal for rebuilding indexes in
24/7 environments.
|
While REBUILD will essentially achieve
the same thing as dropping and re-creating an index, it offers a number
of important advantages. First, the ONLINE option can be used,
which allows continued user access. Second, dropping and re-creating a
clustered index will cause all nonclustered indexes to be rebuilt twice;
the clustered index key is included in the leaf level of each
nonclustered index. When the clustered index is dropped, the table is
converted to a heap, with the nonclustered indexes changing their
leaf-level row locators to a row ID containing the file, page, and row
number. When the clustered index is re-created, the row ID changes back
to the clustered index key. When the REBUILD option is used, all of this is avoided, with the clustered index key remaining unchanged throughout.
|
Table 1
summarizes the appropriate index-maintenance technique based on the
fragmentation level. Although indexes of any fragmentation level will
benefit from the REBUILD method, in order to limit the
transaction log usage (and subsequent effects on database mirroring
and/or log shipping), it's recommended that only indexes with greater
than 30 percent fragmentation use REBUILD, with tables having fragmentation levels lower than this value using REORGANIZE.
The 30 percent level is a general recommendation only and is subject to
the local nuances of the database and its environment. The overall goal
is to strike a balance between transaction log size, user impact, and
increased index performance.
Table 1. Fragmentation levels and options
| Index fragmentation | Maintenance technique |
|---|
| 5%-30% | ALTER INDEX REORGANIZE |
| 30% + | ALTER INDEX REBUILD |
An alternative to REBUILD is CREATE WITH DROP_EXISTING.
CREATE WITH DROP_EXISTING
The CREATE WITH DROP_EXISTING command,
an example of which follows, allows the index definition to be modified
as part of the re-creation process, for example, adding additional
columns or moving the index to another filegroup.
-- Rebuild an index with an altered definition
CREATE CLUSTERED INDEX cixClientSSN
ON dbo.client(SSN, DOB)
WITH (DROP_EXISTING = ON)
Using CREATE WITH DROP_EXISTING is
particularly useful for rebuilding clustered indexes on tables with one
or more nonclustered indexes. Unlike a traditional drop/re-create, which
rebuilds the nonclustered indexes twice, nonclustered indexes are
rebuilt only once using this option—or not at all if the index
definition remains the same. On the downside, CREATE WITH DROP EXISTING operates on a single index at a time, in contrast to the ALL option of the REBUILD/REORGANIZE commands covered earlier.
Each of the index-maintenance techniques we've
covered thus far can be executed with a large number of optional
parameters.
Before we close this section, let's cover some frequently used ones.
Index options
Let's recap these options and cover some other
commonly used ones
FILLFACTOR—When building an index, FILLFACTOR determines how full (as a percentage) each index page will be for existing data. By default, FILLFACTOR is 0
(which is equivalent to 100), which means index pages will fill to
capacity. This has two implications: with a higher page fill rate, fewer
pages need to be read, but on the downside, a high update rate will
result in more page splits and fragmentation. For tables with a high
update rate, a lower fill factor may
increase performance and reduce fragmentation, although the trade-off is
that the index (and therefore the database) will occupy more space
because each page is less than 100 percent full.
PAD_INDEX—FILLFACTOR applies at the leaf level of index pages only. When the PAD_INDEX option is set to ON, the FILLFACTOR setting is applied to intermediate index levels as well.
MAXDOP—This
option allows the number of CPUs to be limited to (or increased from)
the default server setting; for example, if the default server MAXDOP setting is set to 1 to prevent parallel queries, MAXDOP = 0 can be specified to maximize the performance of the index operation.
SORT_IN_TEMPDB—By
default, the temporary disk space used during index rebuild operations
comes from the user database containing the index. This option directs
the operation to use the tempdb database, which may improve rebuild
performance, particularly when tempdb is located on dedicated disks.
When using this option, take care to ensure tempdb has sufficient disk
space available.
DATA_COMPRESSION— individual tables and indexes can be compressed, using either the ROW or PAGE method.
An example of using these options as part of an index rebuild follows:
-- Rebuild an index with various custom options
ALTER INDEX IX_SalesOrderHeader_CustomerID
ON Sales.SalesOrderHeader
REBUILD WITH (
FILLFACTOR = 80
, PAD_INDEX = ON
, MAXDOP = 0
, SORT_IN_TEMPDB = ON
, DATA_COMPRESSION = PAGE
)
