The Index Fragmentation alarm is raised when the largest Index file on the SQL Server is more than 5% fragmented. It is a good idea to defragment large index files that are more than 5% fragmented.Edit me
Note: Spotlight only checks large index files, that is Index files that have exceeded the Maximum Size criteria (default 10MB). The Maximum Size criteria can be customized using the Connection Tags feature; refer to the SQL Server | Databases drilldown documentation for more information.
When the alarm is raised:
- Open SQL Server | Databases drilldown | Fragmented Indexes.
- Select the index to defragment and click Fragmented Indexes | Generate defragmentation script.
- Use SQL Server Management Studio to schedule a job to run this script during a quiet period.
Tip: Choose a quiet time to run this script. Execution of this script could put significant load on the SQL Server.