Defragment Indexes

To defragment an index

Select Tools | Administer | Index Fragmentation.

Note: You can also right-click an index from the Indexes node and select Defragment Index.
Select a database.

Tip: To narrow the search results enter a table or view name.
Click Search.

Note: You can set the scanning mode:
- Limited—Provides fastest performance, only the parent-level pages are scanned. Fragmentation percent and Page Space Used percent can display as null.
- Sampled—Provides estimated index statistics, based on 1% sample of all pages. This mode does not show heap indexes fragmentation percent.
- Detailed—Provides actual index statistics, all pages are scanned.
Select one or more indexes.

Notes:
- To select indexes with Critical and Warning or Critical Only fragmentation use buttons in the lower part of the pane.
- You can set fragmentation thresholds for issuing warning and critical statuses in Options.
- Columnstore indexes cannot be defragmented. Right-click a columnstore index and select Tasks | Rebuild Index instead.
Click .

Complete the fields as necessary. Review the following for additional information:

Fill factor setting

Select one of the following options:

OLTP—Select when working with intensively changing data. The fill factor value is automatically set to 85.
Data warehouse—Select when working with rarely changing and especially with stored data. The fill factor value is automatically set to 100.
Custom—Select to manually enter the fill factor value.