Create SQL Server instance index defragmentation policies

Applies to

ApexSQL Defrag


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.


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:

Create index defragmentation policy in SQL index tool

Once the Create policy window is opened, provide the new policy name in the General tab, and optionally its description:

Choose name for index defragmentation policy in SQL index tool

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:

Choose target of index defragmentation policy in SQL index tool

Quick tip icon

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:

Rebuild operation type

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:

Thresholds tab in SQL index tool

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:

Index fragmentation thresholds selection in SQL index software

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:

Fragmentation scan mode selection in SQL index tool

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:

Policy resource thresholds in SQL index software

Under the Schedule tab, set date and time when this policy will start as well as its frequency and duration:

Scheduled index defragmentation policy in SQL index tool

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:

Add email address in SQL index tool

Once the desired index defragmentation policy settings are configured, click the Finish button to save policy and close the Create policy window:

Configure email policy notification settings in SQL index tool

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

Index defragmentation policy display in SQL index tool

The policy will start based on its schedule. To start it manually tick the policy checkbox and click the Run button:

Run index defragmentation policy in SQL index tool

Once the policy is completed, an email is received with its outcome status:

Email policy notification in SQL index tool

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:

Edit, delete, enable or disable policies in SQL index tool

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.