Reorganizing and Rebuilding Indexes

Artigo
12/03/2008

The SQL Server 2005 Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. For more information, see this Microsoft Web site.

In SQL Server 2005 you can remedy index fragmentation by either reorganizing an index or by rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or on a single partition of an index.

Detecting Fragmentation

The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

The algorithm for calculating fragmentation is more precise in SQL Server 2005 than in SQL Server 2000. As a result, the fragmentation values will appear higher. For example, in SQL Server 2000, a table is not considered fragmented if it has page 11 and page 13 in the same extent but not page 12. However, to access these two pages would require two physical I/O operations, so this is counted as fragmentation in SQL Server 2005.

The result set returned by the sys.dm_db_index_physical_stats function includes the following columns.

Column	Description
avg_fragmentation_in_percent	The percent of logical fragmentation (out-of-order pages in the index).
fragment_count	The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pages	Average number of pages in one fragment in an index.

After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.

These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment.

Very low levels of fragmentation (less than 5 percent) should not be addressed by either of these commands because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the cost of reorganizing or rebuilding the index.

Note

In general, rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. For more information about mixed extents, see Pages and Extents.

Example

The following example queries the sys.dm_db_index_physical_stats dynamic management function to return the average fragmentation for all indexes on the Production.Product table. By using the previous table, the recommended resolution is to reorganize PK_Product_ProductID and rebuild the other indexes.

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

The statement might return a result set similar to the following.

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

Reorganizing an Index

To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause. This statement replaces the DBCC INDEXDEFRAG statement. To reorganize a single partition of a partitioned index, use the PARTITION clause of ALTER INDEX.

Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.

Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.

The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates.

Reorganize an index when the index is not heavily fragmented. See the previous table for fragmentation guidelines. However, if the index is heavily fragmented, you will achieve better results by rebuilding the index.

Large Object Data Type Compaction

Besides reorganizing one or more indexes, large object data types (LOBs) that are contained in the clustered index or underlying table are compacted by default when an index is reorganized. The data types image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml are large object data types. Compacting this data can cause better disk space use:

Reorganizing a specified clustered index will compact all LOB columns that are contained in the leaf level (data rows) of the clustered index.
Reorganizing a nonclustered index will compact all LOB columns that are nonkey (included) columns in the index.
When ALL is specified, all indexes associated with the specified table or view are reorganized and all LOB columns associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.
The LOB_COMPACTION clause is ignored if LOB columns are not present.

Rebuilding an Index

Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.

The following methods can be used to rebuild clustered and nonclustered indexes:

ALTER INDEX with the REBUILD clause. This statement replaces the DBCC DBREINDEX statement.
CREATE INDEX with the DROP_EXISTING clause.

Each method performs the same function, but there are advantages and disadvantages to consider as shown in the following table.

Functionality	ALTER INDEX REBUILD	CREATE INDEX WITH DROP_EXISTING
Index definition can be changed by adding or removing key columns, changing column order, or changing the column sort order.*	No	Yes**
Index options can be set or modified.	Yes	Yes
More than one index can be rebuilt in a single transaction.	Yes	No
Most index types can be rebuilt online without blocking running queries or updates.	Yes	Yes
Partitioned index can be repartitioned.	No	Yes
Index can be moved to another filegroup.	No	Yes
Additional temporary disk space is required.	Yes	Yes
Rebuilding a clustered index rebuilds associated nonclustered indexes.	No Unless the keyword ALL is specified.	No Unless the index definition changed.
Indexes enforcing PRIMARY KEY and UNIQUE constraints can be rebuilt without dropping and re-creating the constraints.	Yes	Yes
Single index partition can be rebuilt.	Yes	No

* A nonclustered index can be converted to a clustered index type by specifying CLUSTERED in the index definition. This operation must be performed with the ONLINE option set to OFF. Conversion from clustered to nonclustered is not supported regardless of the ONLINE setting.

** If the index is re-created by using the same name, columns and sort order, the sort operation may be omitted. The rebuild operation checks that the rows are sorted while building the index.

You can also rebuild an index by first dropping the index with the DROP INDEX statement and re-creating it with a separate CREATE INDEX statement. Performing these operations as separate statements has several disadvantages, and we do not recommend this.

Disabling Nonclustered Indexes to Conserve Disk Space During Rebuild Operations

When a nonclustered index is disabled, the index data rows are deleted, but the index definition remains in metadata. The index is enabled when it is rebuilt. When the nonclustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index. However, by disabling and rebuilding a nonclustered index in separate transactions, the disk space made available by disabling the index can be reused by the subsequent rebuild or any other operation. No additional space is required except for temporary disk space for sorting, which is typically 20 percent of the index size. If the nonclustered index is on the primary key, any active, referencing FOREIGN KEY constraints will automatically be disabled. These constraints must be manually enabled after the index is rebuilt. For more information, see Disabling Indexes and Guidelines for Enabling Indexes and Constraints.

Rebuilding Large Indexes

Indexes with more than 128 extents are rebuilt in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted and then moved to new allocation units created to the store the rebuilt index. In the physical phase, the allocation units previously marked for deallocation are physically dropped in short transactions that happen in the background, and do not require many locks. For more information, see Dropping and Rebuilding Large Objects.

Setting Index Options

Index options cannot be specified when reorganizing an index. However, the following index options can be set when you rebuild an index by using either ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING:

PAD_INDEX	DROP_EXISTING (CREATE INDEX only)
FILLFACTOR	ONLINE
SORT_IN_TEMPDB	ALLOW_ROW_LOCKS
IGNORE_DUP_KEY	ALLOW_PAGE_LOCKS
STATISTICS_NORECOMPUTE	MAXDOP

Note

If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

Additionally, the SET clause in the ALTER INDEX statement lets you set the following index options without rebuilding the index:

ALLOW_PAGE_LOCKS	IGNORE_DUP_KEY
ALLOW_ROW_LOCKS	STATISTICS_NORECOMPUTE

For more information, see Setting Index Options.

To rebuild or reorganize an index

ALTER INDEX (Transact-SQL)

To rebuild an index by dropping and re-creating an index in one step

CREATE INDEX (Transact-SQL)

Examples

A. Rebuilding an index

The following example rebuilds a single index.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. Rebuilding all indexes on a table and specifying options

The following example specifies the keyword ALL. This rebuilds all indexes associated with the table. Three options are specified.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Reorganizing an index with LOB Compaction

The following example reorganizes a single clustered index. Because the index contains a LOB data type in the leaf level, the statement also compacts all pages that contain the large object data. Note that you do not have to specify the WITH (LOB_Compaction) option because the default value is ON.

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Change History

Release	History
17 July 2006	New content: Added information about the algorithm for calculating fragmentation in the section "Detecting Fragmentation."
5 December 2005	New content: Added guidance on how to handle very low levels of fragmentation in the section "Detecting Fragmentation."

Compartilhar via