Export (0) Print
Expand All

How to: Reorganize an Index (SQL Server Management Studio)

This topic describes how to reorganize an index or all indexes on a table by using Object Explorer in SQL Server Management Studio. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value. For more information, see Reorganizing and Rebuilding Indexes.

To reorganize an index

  1. In Object Explorer, connect to an instance of the Database Engine and then expand that instance.

  2. Expand Databases, expand the database that contains the table with the specified index, and then expand Tables.

  3. Expand the table in which the index belongs and then expand Indexes.

  4. Right-click the index to reorganize and then click Reorganize.

  5. To specify that all pages that contain large object (LOB) data are also compacted, select the Compact large object column data check box.

    The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use.

  6. To disable large object data compaction, clear the Compact large object column data check box.

  7. To start the reorganize operation, click OK.

To reorganize all indexes on a table

  1. In Object Explorer, connect to an instance of the Database Engine and then expand that instance.

  2. Expand Databases, expand the database that contains the table with the specified indexes, and then expand Tables.

  3. Expand the table in which the indexes belong.

  4. Right-click Indexes and then click Reorganize All.

  5. To specify that all pages that contain large object (LOB) data are also compacted, select the Compact large object column data check box.

    The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use.

  6. To disable large object data compaction, clear the Compact large object column data check box.

  7. To start the reorganize operation, click OK.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft