Applies to
Summary
The following guide gives an insight into the topic of SQL Server instance defragmentation policies, what are they used for and how are they set inside the SQL index software.
Description
As more load and more traffic occur on a database the more fragmented their indexes become. And the more fragmented indexes are the less efficient data traffic and overall data flow is.
This largely decreases the performance of that database so to remedy it, the defragmentation of those indexes needs to take place.
Fragmentation is a common term when files are broken into pieces and spread across several locations on a disk, which largely affects the reading or writing time of those files. On the other hand, defragmentation implies reorganizing those files so all it’s related pieces are put back together and stored in one location (in consecutive order).
Two available operations for the SQL Server instance index defragmentation process are to reorganize and rebuild indexes. Index reorganization solves fragmentation in a less effective way, since it is a pure cleanup operation, whereas index rebuild is a more powerful solution and builds indexes “from scratch”.
For more information on the virtues and flaws of both mentioned operations, consult the Why, when and how to rebuild and reorganize SQL Server indexes article.
Index defragmentation policies are offered by the SQL index application as an index maintenance solution. They include both index defragmentation operations and are easy to setup and use.
Configure defragmentation policies
To create a SQL Server instance index defragmentation policy, from the ApexSQL Defrag tool head over to the Policies tab, click the Create drop down button and select the Custom option:
Once the Create policy window is opened, provide the new policy name in the General tab, and optionally its description:
To choose the target for the policy, click the “three-dotted” browse button inside the Targets box. In the Policy targets window choose SQL Server instances to apply the policy to, or any of their individual databases. Click the OK button to confirm selection and close the window:
|
Quick tip: To ensure no exclusive locks will be held on the target database objects that will result in decreased database performance, make sure to select the online rebuild option:
|
In the Thresholds tab the thresholds related to the index fragmentation and resource usage are presented as well as the Fragmentation scan mode types. These thresholds determine at what index state what operation will be executed by the policy:
By default, indexes that have fragmentation between 10%-30% will be reorganized, and indexes with fragmentation percentage above 30% will be rebuilt. These settings can be easily changed by manually dragging the corresponding sliders and set their own thresholds:
In the next step, select preferred SQL Server instance index fragmentation scan mode. By default, the Limited is chosen and it’s the mode that scans the smallest number of pages and it is the fastest.
Sampled mode scans only 1% of all pages in an index or a heap where Detailed scan mode scans all pages and is the slowest but the most accurate:
Under the Resource thresholds section, there are options to set thresholds for CPU load, available RAM memory, hard disk usage, etc that will determine conditions for whether the policy will start or not:
Under the Schedule tab, set date and time when this policy will start as well as its frequency and duration:
In the Notification tab, provide an email account to be notified on the following SQL Server instance defragmentation policy outcomes: success, failure, policy job cancelation, warnings or job expiration. To enter an email account, click the Add button and provide the address. Click the OK button to save the entry:
Once the desired index defragmentation policy settings are configured, click the Finish button to save policy and close the Create policy window:
The newly created policy can be seen in the policy list along with the following details that are neatly gathered in the following grid columns:
Result – where the status of last policy execution is kept
Name – name of the SQL Server instance defragmentation policy
Enabled – shows if policy is enabled or disabled
Schedule – the information about the policy schedule is displayed
Status – the current status of the policy, is it in running or in idle state
Next run – date/time of next policy execution
Last run – date/time when the policy last ran
Rebuild – if the policy uses online or offline index rebuild
Message – if anything goes wrong during the policy runtime, the appropriate message will be shown
The policy will start based on its schedule. To start it manually tick the policy checkbox and click the Run button:
Once the policy is completed, an email is received with its outcome status:
Additionally, the policy can be easily edited, deleted enabled or disabled for a desired time frame by selecting one of the matching buttons from the ribbon bar or by choosing the same option from the right-click button menu:
With the explained use of the index defragmentation tool, the index performance, which carries a lot of weight in overall SQL Server instance performance, can be constantly maintained at a high level in an automated manner like this. Also, different policies can be created for different instances based on their needs.
