There are two different
aspects to index management. The first aspect is the management of
indexes by the SQL Server database engine. Fortunately, the engine does a
good job of managing the indexes internally so that limited manual
intervention is required. This is predicated on a well-designed database
system and the use of SQL Server features, such as automatic updates to
distribution statistics.
The other aspect of index
management typically comes into play when performance issues arise.
Index adjustments and maintenance of these indexes make up the bulk of
this effort.
Managing Indexes with T-SQL
One of the T-SQL features available with SQL Server 2008 is the ALTER INDEX
statement. This statement simplifies many of the tasks associated with
managing indexes. Index operations such as index rebuilds and changes to
fill factor that were previously handled with DBCC commands are now
available via the ALTER INDEX statement. The basic syntax for ALTER INDEX is as follows:
ALTER INDEX {index_name | ALL}
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name}
{ REBUILD [WITH(<rebuild_index_option>[,...n])]
| REORGANIZE [ WITH( LOB_COMPACTION = {ON | OFF})]
| DISABLE
| SET (<set_index_option>[,...n]) }
Let’s look at a few examples that demonstrate the power of the ALTER INDEX statement. The first example simply rebuilds the primary key index on the Production.Product table:
ALTER INDEX [PK_Product_ProductID] ON [Production].[Product] REBUILD
This offline operation is equivalent to the DBCC DBREINDEX
command. The specified index is dropped and re-created, removing all
fragmentation from the index pages. This is done dynamically, without
the need to drop and re-create constraints that reference any of the
affected indexes. If it is run on a clustered index, the data pages of
the table are defragmented as well. If you specify the ALL option for the ALTER INDEX command, all indexes as well as the data pages of the table (if the table has a clustered index) are defragmented.
Note
If the REBUILD
option is run on a heap table (that is, a table with no clustered
index), the rebuild operation does not affect the underlying table. Only
the specified nonclustered indexes are rebuilt.
For added flexibility, you can also specify index options as part of the REBUILD operation. The options available with the REBUILD command are the same options available when you are creating indexes. The only exception is that the DROP EXISTING option is not available with the REBUILD operation. The following example rebuilds the clustered index on the Production.Product table and specifies several of the available REBUILD options:
ALTER INDEX [PK_Product_ProductID]
ON [
Production].[Product] REBUILD WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF,
DATA_COMPRESSION = NONE )
An alternative to the REBUILD operation is the REORGANIZE operation. The REORGANIZE operation is equivalent to the DBCC INDEX DEFRAG command. During the REORGANIZE
operation, the leaf-level pages of the index are physically reordered
to match the logical order of the index keys. The indexes are not
dropped. The REORGANIZE operation is always an online operation and does not require long-term table locks to complete.
Tip
The REORGANIZE
operation can generate a large number of transactions during its
execution. You need to be sure to carefully evaluate the amount of space
available in the transaction log and monitor the free space during this
operation. If the transaction log is set to AUTOGROW,
you need to make sure you have adequate free space on the drive where
your transaction log lives. This is especially true for very large
tables. Several options are available for mitigating the growth of the
log during these operations, such as setting the recovery model on the
database to BULK-LOGGED.
The REORGANIZE operation has just one option: LOB_COMPACTION. When the LOB_COMPACTION option is set to ON,
the data for columns with large object (LOB) data types is compacted.
This consolidates the data and frees disk space. LOB data types include image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. The following example performs a REORGANIZE operation on the clustered index of the Production.Product table with the LOB_COMPACTION option set to OFF:
ALTER INDEX [PK_Product_ProductID] ON [Production].[Product]
REORGANIZE WITH ( LOB_COMPACTION = ON )
Disabling an index is another capability introduced with SQL Server 2005 that can be accomplished with the ALTER INDEX statement. When the DISABLE
option is used on an index, the index is no longer available for
retrieving data from a table. If a clustered index is disabled, the
entire table is made unavailable. The data remains in the table, but no
Data Manipulation Language (DML) operations can be performed on the
table until the index is dropped or rebuilt. Unlike dropping an index,
when an index is disabled, SQL Server retains the index definition in
metadata so it can easily be re-enabled; index statistics are still
maintained for nonclustered indexes that have been disabled.
After an index is disabled, you can re-enable it only by re-creating the index. You can accomplish this using the ALTER INDEX REBUILD command or CREATE INDEX WITH DROP_EXISTING command.
Disabling indexes can be
particularly useful for testing purposes. Let’s say you have a
nonclustered index on a table that you believe is used very little. You
can disable the index initially before removing it to evaluate the
change. The definition of the index is still contained in the database.
If you ultimately determine that the index is still needed, you can
rebuild the index to make it available again.
Tip
Another reason for
disabling a nonclustered index is to reduce the space requirements when
rebuilding the index. If an index to be rebuilt is not disabled, SQL
Server requires enough temporary disk space in the database to store
both the old and new versions of the index. However, if the index is
disabled first, SQL Server can reuse the space required for the disabled
index to rebuild it. No additional disk space is necessary except for
temporary space required for sorting, which is only about 20% of the
index size.
The following example disables a nonclustered index on the Production.Product table:
ALTER INDEX [AK_Product_Name] ON [Production].[Product] DISABLE
One point to keep in mind when
an index is disabled is that it is not readily apparent in SSMS that
the index has been disabled. The index still appears in the Object
Explorer tree under the Indexes node,
and there are no indicators on the index display to alert you to the
fact that it has been disabled. You can, however, use other methods to
determine if the index has been disabled. The sys.indexes catalog view is one of these methods. Refer to the is_disabled column returned with this view. A value of 1 in the is_disabled column indicates that it has been disabled, and a value of 0 indicates that it is enabled. The following SELECT statement shows an example of how to use the sys.indexes catalog view:
select is_disabled,* from sys.indexes
where object_name(object_id) = 'Product'
You can also easily change options on an index with the ALTER INDEX statement. The following example sets several of the available options for a nonclustered index on the authors table:
ALTER INDEX [AK_Product_ProductNumber] ON [Production].[Product]
SET (
ALLOW_PAGE_LOCKS = ON,
ALLOW_ROW_LOCKS = OFF,
IGNORE_DUP_KEY = ON,
STATISTICS_NORECOMPUTE = ON
)
Other options exist for managing indexes with T-SQL, but the ALTER INDEX
statement provides the bulk of what you need. Many of the other T-SQL
options that you may have used for managing indexes in SQL Server 2000
and earlier, such as DBCC DBREINDEX, are still available in SQL Server 2008 for backward compatibility.
Managing Indexes with SSMS
Several tools are available in
SSMS for managing indexes. You can use tools such as the Database Engine
Tuning Advisor, database diagrams, and the Table Designer to view
indexes and make modifications. These tools have many features that are
geared toward specific tasks, but again, in most cases the Object
Explorer provides the simplest means for managing indexes.
Figure 1
shows the index options available by right-clicking an index in the
Object Explorer. Many of these options are geared toward index
management, including the options Rebuild, Reorganize, and Disable.
