Reorganizing and Rebuilding Indexes
The SQL Server 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.
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 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, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. For more information about mixed extents, see Understanding 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
To rebuild an index by dropping and re-creating an index in one step
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
See Also