Applies to
Summary
The following article explains the advanced settings of the SQL Server instance defragmentation policies.
Description
SQL index maintenance software provides an option of automating SQL index defragmentation operations through its policies. Those policies allow an easy and straightforward customization of defragmentation operations on SQL indexes, both rebuild and reorganize types.
When specific resource thresholds are set, those custom tuned values will determine if its defragmentation policy will run when its time is due.
If the policy is prevented for running the first time, the next time the same policy should be executed based on its schedule, the SQL index software will recheck the policy threshold values and determine if this policy is safe to run.
SQL Server instance index policy thresholds
There are two different defragmentation policy thresholds within the SQL index software:
- Index fragmentation thresholds – these thresholds are related to the reorganize/rebuild operations that will be performed within the newly made defragmentation policy
- Resource thresholds – these thresholds are used to determine if the usage of the local machine resources, the machine running the ApexSQL Defrag and its components, has not exceeded the custom set values which will then allow policy to be performed safely
Index fragmentation thresholds
In the Thresholds tab on the left side of the Create policy window, set the preferrable thresholds that will determine if a SQL Server instance policy will start based on its schedule.
The first thresholds available for tuning are the Index fragmentation thresholds:
- Reorganize when greater than – Set this threshold to reorganize all indexes in fragmentation range between specified reorganize and rebuild value
- Rebuild when greater than – Set this threshold to rebuild all indexes with fragmentation levels equal or greater than the specified rebuild value
By default, the policy will reorganize indexes with fragmentation level percentage between 10% and 30%. Also, it will rebuild indexes with fragmentation level percentage that is higher than 30%.
To change these values, simply move available sliders to left or right to set the new threshold values:
Index policy resource thresholds
To check if the machine resources are in high usage in the time when the SQL Server instance index policy will be performed, use policy resource thresholds.
To further explain, a policy will start only if the set resource threshold conditions are met. The following are the SQL Index tool policy thresholds:
- CPU load – ApexSQL Defrag will check value for CPU usage before starting the policy. If it gets the value equal or greater than specified in the box, the job will not start
- Memory available – The software will check value for available RAM memory before starting the policy. If it gets the value equal or lower than specified in the box, the policy will not start
- Transaction log usage – The application will check the value for available transaction log usage before the starting the policy. If it gets the value equal or higher than specified in the box, the SQL Server instance policy will not start
- Hard disk usage – The tool will check value for available hard disk memory before starting the policy. If it gets the value equal or lower than specified in the box, the job will not start
- Active job count – The application will check value for number of currently active jobs before starting the policy. If it gets the value equal or higher than specified in the box, the policy will not start
- Active transactions – The software will check value for number of active transactions before starting the policy. If it gets the value equal or higher than specified in the box, the job will not start
- Retry to check resources after – The tool will re-check the resource values for this SQL Server instance policy after the specified time period in the box
- Exit job as failed after – The policy will not execute after the specified number of failed resource check operations
- Cancel the job run after – The application will cancel the policy after the specified time in the box
|
Quick tip: To see what a specific threshold represents, hover over its tooltip where the explanation of its purpose is placed:
|
SQL index policy thresholds in action
To initiate the process of creating a defragmentation policy, once the SQL index application is started, move to the Policies tab and from the Create drop-down menu, choose the Custom option:
This will open the Create policy window where the name of the SQL Server instance policy, its description, and the target of defragmentation activity can be entered. Additionally, choose an offline or online index rebuild:
In the Thresholds tab, set the desired values for Index fragmentation thresholds and Resource thresholds respectively. For example, the image below shows that the following is set:
- Index fragmentation thresholds – The policy will reorganize indexes with fragmentation level percentage between 10% and 40%, and will rebuild indexes with level percentage above 40%
- Resource thresholds:
- CPU load – If the CPU usage is equal or higher than 80%, the policy will not start
- Memory available – If the available RAM memory is equal or below the 1GB value, the SQL Server instance index policy will not start
- Hard disk usage – If the available hard disk storage space value is equal or below the 2GB, the policy will not start
- Active job count – If the number of active policies is equal or higher than 5, the policy will not start
- Exit job as failed after – If the policy has more than 3 failed resource check activities then it will not be executed again
To continue setting the policy’s schedule and/or notification on its outcome, see the article: How to automate SQL Server defragmentation using policies
Once the SQL Server index policy is created, it can be found in the policy grid with all its relevant details spread across the grid columns. When its schedule time arrives, the SQL index software will check for the current machine resource usage values and compare them with the previously set values which will determine if it’s safe to start the policy:
Conclusion
The described solution is a nifty feature to have when a machine resources tend to be on a higher scale and when putting more strain on a machine is not the best idea nor the welcomed one.
This is a safe guard to a running system ensuring no SQL Server instance index defragmentation activities will start if its resource usage is in high demand.
