Select the index thresholds and analysis frequency
The Thresholds tab allows you to adjust the rules for Fragmentation and Scan Density, and set the frequency of analysis.
This window provides a slider control that allows you to adjust the threshold value that triggers a response for this policy. To change the current threshold value, click the divider button between two levels and drag it left or right to decrease or increase that threshold value.
Recommended fragmentation and scan density ranges
The fragmentation level identifies the amount of fragmentation of a database object. The fragmentation range allows you to define what range of fragmentation levels are displayed as Acceptable, Caution, and Critical.
Indexes typically perform well when fragmentation is less than 30% and scan density is greater than 80%. Select the ranges that are appropriate for your selected target indexes. If you select Fragmentation and Scan Density, both conditions must be met for an index to comply with this policy. Once you specify these threshold levels, SQL Defrag Manager uses these values to determine how fragmentation levels are displayed in the Console. SQL Defrag Manager also uses these values when performing automated responses based on fragmentation levels found during scheduled analysis activities.
How SQL Defrag Manager uses these values
SQL Defrag Manager displays objects with an Acceptable fragmentation level as green, objects with a Caution fragmentation level as yellow, and objects with a Critical fragmentation level as red. On the Summary tab, you can define indexes that are not compliant with this policy as either Caution or Critical.
You can also assign a response for indexes which are not in compliance with this policy. These responses range from creating a To Do item on the SQL Defrag Manager Console To Do tab to automatically rebuilding indexes with the associated fragmentation level.
You can specify custom threshold levels for different database objects. If you have a table that impacts productivity with low levels of fragmentation, you may want to lower the threshold levels for that table. Although you can manage multiple threshold levels across many objects, use standard fragmentation levels across most objects to reduce complexity and to present data consistently in the Console.
Select a frequent analysis renewal for indexes that fragment rapidly due to updates, deletions, and insertions; select less frequency for indexes that are more static.