The Index Fragmentation alarm is raised when the largest index partition on the SQL Server is more than 5% fragmented. It is a good idea to defragment large index partitions that are more than 5% fragmented.
Note: Spotlight only checks large index partitions, that is index partitions 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.