Batch job to handle SQL index defragmentation

Important

Dynamics 365 for Finance and Operations is now being licensed as Dynamics 365 Finance and Dynamics 365 Supply Chain Management. For more information about these licensing changes, see Dynamics 365 Licensing Update.

In Platform update 22, a new system batch job has been introduced to rebuild fragmented indexes. Index fragmentation results in notable performance degradation in specific scenarios. To address fragmentation issues and keep the database in a top-performing state, this batch job will rebuild highly fragmented indexes periodically at a scheduled time. By default, the job scheduled to run at 3:00 AM local time every day for a maximum of 2 hours. If the batch job finds that not many fragmented indexes need to be rebuilt, it will complete early.

This system job cannot be canceled. You can change the schedule and its recurrence if you want to run it weekly. System jobs are expected to run at least once a week.

You can change the parameter values on the Adjust system job parameters page to denote how long the job can run and how many indexes it should target, at a maximum. The DTU threshold is to prevent this job from starting when the system is busy. The default DTU threshold is 50, which means that if the system is using 50 percent or more DTU during the time that the index rebuild job is scheduled to run, the job exit early without rebuilding any indexes.

Screenshot of Adjust system job parameters page

When this job is executing, there could be some impact on the following:

  • The time that it takes SQL resources to process.
  • There may be blocking,

Change the default scheduled time/recurrence

  1. Go to System administration > Inquiries > Batch Jobs.
  2. Search for a job description that contains index rebuild.
  3. Select the record.
  4. Click the menu item Batch Job > Recurrence.
  5. Change the schedule time and recurrence to suit your schedule.

The results of index rebuild can be found by going to System administration > Inquiries > Database > SQL index fragmentation details.

Note

In some cases, you may find that the before and after fragmentation number is the same or even higher. This is typical because Microsoft uses a less intrusive online rebuild option. In the future, we plan to introduce an optional offline rebuild option.