See Also: Main_Page - Database Administration - Database Maintenance - Index Maintenance

New ALTER INDEX Command in SQL Server 2005

With SQL Server 2005 you should maintain indexes using the new ALTER INDEX statement. The goal of index maintenance is removing fragmentation that occurs as a result of adding, modifying or removing data. The higher the fragmentation level, the less effective the index will be. The preferred way of determining the index fragmentation level is by querying avg_fragmentation_in_percent column of the sys.dm_db_index_physical_stats dynamic management function (DMF). This DMF allows retrieving data about a specific partition of an index, specific index, all indexes on a given table, all indexes in a given database or all indexes in all databases. DBCC SHOWCONTIG statement used for retrieving index fragmentation information is deprecated but still supported.

Once you determine the fragmentation level there are two options: 1)if fragmentation is less than 30%, use ALTER INDEX...REORGANIZE 2)if fragmentation is greater than 30%, rebuild the index. Fragmentation levels below 5% will not cause any significant performance impact and can therefore be ignored.

You can rebuild indexes using either ALTER INDEX...REBUILD or CREATE INDEX...WITH DROP_EXISTING construct. Both constructs rebuild an index but each offers certain functionality not supported by the other. For example, CREATE INDEX...WITH DROP_EXISTING allows changing index columns and sort order, re-partitioning indexes, or moving an index to a different filegroup. ALTER INDEX...REBUILD on the other hand supports rebuilding multiple indexes in a single transaction, or rebuilding a single partition of an index. You also have an option of rebuilding an index by running DROP INDEX followed by CREATE INDEX, but the other two alternatives are considerably more efficient from resource utilization as well as availability perspective. For example, the following index rebuilds all indexes online on Production.Product table using ALTER INDEX...REBUILD construct. This statement uses up-to two processors for parallel execution and changes the FILLFACTOR setting:

ALTER INDEX ALL ON Production.Product
  REBUILD WITH (  FILLFACTOR = 90,   SORT_IN_TEMPDB = ON,    ONLINE = ON,  MAXDOP = 2)

Next statement uses CREATE INDEX WITH DROP_EXISTING clause to move an index to a different filegroup:

CREATE UNIQUE NONCLUSTERED INDEX  AK_Product_ProductNumber  ON  Production . Product
    (       ProductNumber  ASC  )
    WITH ( SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON,
          ONLINE = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)
   ON  SECONDARY

Rebuilding Indexes Online with Enterprise Edition

With SQL Server 2005 Enterprise Edition you can create, drop or rebuild indexes online using one of the following constructs:

• ALTER INDEX...REBUILD
• CREATE INDEX
• CREATE INDEX...WITH DROP_EXISTING
• DROP INDEX
• ALTER TABLE ADD CONSTRAINT (for primary keys and unique constraints)
• ALTER TABLE DROP CONSTRAINT (for primary keys and unique constraints)

In addition, reorganizing an index is always an online operation.

Table Locks During Index Rebuilds

By default, when you rebuild an index SQL Server maintains table level lock on the underlying object, thereby preventing or severely limiting access to the table (or view) for which you are rebuilding an index. ONLINE option of ALTER INDEX statement alters the behavior of the command so that users can continue reading and writing data to the table while index is being rebuilt. Instead of acquiring a table lock for the duration of the index rebuild SQL Server behaves as follows:

1. Acquires a schema-sharing lock for a brief period at the beginning. This is referred to as the preparation phase.
2. Holds intent-shared lock is during the main phase of rebuild. This is referred to as the build phase.
3. Places schema modification lock on the table at the end of the operation if you are rebuilding the clustered index, or a shared lock if you're rebuilding a non-clustered index. This is referred to as the final phase.

During the preparation phase of index build (or re-build) SQL Server defines a snapshot of the table using row-versioning to ensure transaction level read consistency. This phase is normally very short; however, if there are any update transactions SQL Server will wait for them to complete before defining the table's snapshot. Once snapshot definition begins any write operations will be blocked until the preparation phase completes. SQL Server also defines an empty target index during the preparation phase. If you are rebuilding a clustered index SQL Server creates an additional temporary non-clustered mapping index which is used to identify the records to be deleted in the new indexes being built as concurrent transactions add, modify or remove data in the underlying table. The temporary mapping index will have one row for each row in the table.

During the build phase SQL Server scans the source data, orders it as appropriate and populates the target index created in the previous step. An intent-shared lock is held on the source table so that no other indexes can be rebuilt and no schema modifications can take place. You will notice the INDEX_BUILD_INTERNAL_RESOURCE resource lock during the build phase. Users can continue reading and writing data to the table. If data is indeed INSERTED, UPDATED or DELETED in the table while the temporary index is populated SQL Server will apply those changes to the temporary index as well.

During the final phase, SQL Server updates the metadata to replace the source index with the newly created (target) index and subsequently drops the source structure. The final phase will wait until all uncommitted update transactions complete before proceeding. If the clustered index is being rebuilt, SQL Server will acquire a schema modification lock on the table; for non-clustered indexes it will use a shared lock. After the final phase is completed all existing query execution plans are invalidated so that any subsequent queries will use the new index.

Online Index Operations Don't Work For Everything

Online index operations are NOT supported for:

• XML indexes
• Disabled indexes
• Partitioned indexes
• Clustered indexes on tables which contain LOB data type (IMAGE, TEXT, NTEXT, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) and XML) columns
• Non-clustered indexes defined on columns with LOB data types.
• Local temporary tables

Removing Inconsistencies

At times it is possible to remove inconsistencies reported by DBCC CHECKTABLE or DBCC CHECKDB by rebuilding non-clustered indexes on the affected table. Unfortunately you won't be able to remove such inconsistencies while rebuilding the index online because the existing index will be used as the source for the new index. If you do wish to resolve such problem be sure to rebuild index offline thereby forcing SQL Server to scan the clustered index or the table for re-computing non-clustered index values.

Disk Requirements During Online Index Rebuilds

If you use the CREATE INDEX WITH DROP EXISTING construct to re-build a clustered index online any non-clustered indexes on the same table will also be rebuilt online. Indexes will be rebuilt sequentially, one at a time, so additional temporary disk space requirement (necessary for sorting data) will be as large as the largest index on the table.

Building indexes online provides better availability for your databases, but comes with a potential performance penalty: if you anticipate heavy data modification activity on a table building indexes online could take significantly longer than building the same index offline. Furthermore, the INSERT, UPDATE and DELETE operations on tables for which you're performing online index rebuilds will also be slower because SQL Server has to update not only the existing indexes, but also the temporary indexes. This could have particularly heavy impact on CPU utilization.

Disk space requirements for online index rebuilds are normally the same as for offline rebuilds, except for clustered indexes, which require a temporary non-clustered mapping index. If you have tempdb database created on a drive (or drive array) separate from the data volume you could anticipate better performance if you use SORT_IN_TEMPDB option. Keep in mind, however, that if you use this option then all objects necessary for index creation or re-build must fit into tempdb.

Disabling Indexes

SQL Server 2005 allows a database administrator to disable an index using ALTER INDEX statement. No user query can use the index while it is disabled. If the clustered index is disabled then corresponding table data will not be accessible any attempt to retrieve data from a table with disabled clustered index will result in an error similar to the following:

Msg 8655, Level 16, State 1, Line 1  The query processor is unable to produce a plan because the index 'PK_DimProduct_ProductKey' on   table or view 'dimProduct' is disabled.

If you disable the non-clustered index on a table SQL Server will physically delete index data. Similarly if you delete the clustered index on a view the index data will be deleted. You can query is_disabled column of sys.indexes catalog view to determine whether an index has been disabled.

To re-enable the index you could use either ALTER INDEX REBUILD statement or CREATE INDEX WITH DROP_EXISTING statement. You might wish to disable non-clustered indexes before they're rebuilt. If you simply use ALTER INDEX statement to rebuild a non-clustered index SQL Server must maintain the old copy of the index along with the new copy (while it is being built), which might require significant disk space. If the non-clustered index is disabled prior to being rebuilt SQL Server only needs disk space for the new copy of the index. You might also want to disable an index temporarily for troubleshooting performance issues without deleting the index definition. SQL Server could also disable indexes during an upgrade (applying a hotfix or a service pack) if it cannot guarantee data integrity. If this happens SQL Server will record a message in the error log so that you can later rebuild the disabled index.

Parallelism for Index Rebuilds

SQL Server 2005 Enterprise Edition supports using multiple processors by the following index creation and rebuild statements:

• CREATE INDEX
• DROP INDEX (only for clustered indexes)
• ALTER TABLE ADD CONSTRAINT (only for index constraints)
• ALTER TABLE DROP CONSTRAINT (only for clustered indexes)

The number of processors used depends on the 'max degree of parallelism' configuration option and the amount of free resources at the time when the index is being created to rebuilt. Using all available processors for building large indexes would provide the best performance; however, this could also cause severe shortage of resources for user queries. Therefore if SQL Server determines that the system is busy it will only use a subset of processors for index operations. You can impose further restrictions for processor usage on index operations by using MAXDOP query hint, for example, the following statement limits the ALTER INDEX statement to 2 processors:

ALTER INDEX PK_DimProduct_ProductKey ON DimProduct REBUILD  WITH (MAXDOP = 2)

If MAXDOP hint specifies 1 all index operations will be sequential and not parallel; 0 is the default value and does not restrict the number of processors. Any value above 1 will be interpreted as number of processors to be considered, however, the actual number of processors used for building an index might be lower depending on system load.

Parallel index operations might be particularly resource intensive for non-aligned partitioned indexes. This is because SQL Server must build one sort table for each partition (either on the respective file group where the partition is stored or in tempdb, depending on whether SORT_IN_TEMPDB option is used). If the index is aligned with the table partitioning scheme or with the clustered index then sort tables are built sequentially for each partition. But for non-aligned indexes all sort tables are built in one operation unless you set maximum degree of parallelism to 1. The higher the degree of parallelism the more sort tables must be built for non-aligned partitioned indexes at the same time and therefore, the more memory will be required. If the system doesn't have enough memory to create enough sort tables for an index on a table with numerous partitions the operation will fail. If so, you can normally work around the problem by specifying lower degree of parallelism.

More SQLServerPedia Articles on Indexes

How SQL Server Indexes Work

• Index Overview - the basics of how indexes work.
• Data Pages & Extents - about how indexes and data are stored on disk.
• Table and Record Structure - more about the mechanics of data storage.
• Effects of Data Modifications - the more indexes you have, the longer inserts/updates/delete take.
• Index Statistics - the basics of how statistics affect query execution.

Types of Indexes in SQL Server

• Included Columns - starting with SQL Server 2005, you can add fields to indexes with a smaller performance hit.
• Indexed Views - how to build indexes on top of views for more speed.
• XML Indexes Overview - how to index XML documents and fragments.
• XML Index Rules - some additional considerations and requirements for XML indexes.

Best Practices on How to Design Database Indexes

• Indexing Strategies - choosing between clustered/nonclustered, long/short, selectivity, and more.
• Best Practices for Creating Indexes - tips to start with when designing a new index strategy.
• Choosing Index Keys - how to pick which fields to use for a primary key.
• Index Selectivity and Column Order - how to pick which columns to put first, and how it affects different kinds of queries.
• Wide Index Performance - does adding more fields to an index cause it to run slower?
• Tuning Indexes - including articles on the Index Tuning Wizard and Database Tuning Advisor.

Maintaining Indexes for Top Performance

Indexes need regular maintenance in order to perform well.

• Index Maintenance Tutorial - video and script explaining how to defragment and rebuild your indexes automatically.
• Index Maintenance in SQL Server 2005 and Beyond - SQL 2005 introduced new syntax to ALTER INDEX.
• Index Maintenance Overview - analyzing fragmentation and statistics to get the best performance possible out of your indexes.