Configure index fragmentation thresholds
Index Manager allows adjusting the default behavior of displaying index scan results with the help of the Options dialog in the Index Manager: [database_name] document.
To adjust the default scan results:
1. In the Index Manager: [database_name] document, click Options to open the Options dialog. See Index Manager options
2. In the dialog, customize the options and then click OK.
3. To apply the new options, click Reanalyze.
Index Manager options
The Options dialog contains the following options:
| Option Group | Option Name | Description | Default State |
|---|---|---|---|
| Index Fragmentation | Reorganize threshold: % Fragmentation | Determines a fragmentation level at which an index should be reorganized. When the fragmentation level of an index reaches this threshold, it is considered acceptable and will not be reorganized. The reorganize threshold can be set to any value between 1% and 100%. |
10% |
| Index Fragmentation | Rebuild threshold: % Fragmentation | Determines a fragmentation level at which an index should be rebuilt. When the fragmentation level of an index exceeds this threshold, it will be rebuilt to optimize its performance. However, if the fragmentation level of the index falls below the rebuild threshold, it will not be rebuilt. The rebuild threshold can be set to any value between 1% and 100%. |
30% |
| Index Fragmentation | Minimum index size | Indicates the smallest size an index must have to be included in index analysis and management operations. For example, if this value is set to 100 pages, any index with fewer than 100 pages will be excluded from the analysis. | 100 pages |
| Index Rebuild | Sort in tempdb | Stores the temporary sort results to tempdb during the index rebuild. | Enabled |
| Index Rebuild | Online | Allows rebuilding indexes while the database remains online and available for use. For example, when one user is rebuilding an index, other users can update and query the table data without interruption. | Disabled |
In the Options dialog, you can do the following:
- To restore the default options, click Restore.
- To save the changes, click OK.
- To discard the changes, click Cancel.