Reduce long synchronization times

When deploying a modelstore one of the steps is to synchronize the database. If the modelstore contains new or altered indexes synchronization times can rapidly increase causing long wait times before the system is up and running again. Synchronization times can drastically be reduced by increasing the max degree of parallelism option (maxdop) of SQL server, this enables SQL server to use multiple cores / processors for one task. On a SQL server that hosts the AX database however this value is usually set to 1 as that’s the best practice.

18 minutes might not seem too long, just wait and see what happens when an index is altered on a table that contains a couple of 100 million rows.

Standard AX creates indexes as shown below (I’ve never seen AX alter an index, it just seems to drop and create a new index):

With the create index statement above you get a SQL Server cpu utilization like this:

Index creation can benefit from parallelism by adding the maxdop option like below:

The creation time is significantly shorter at the cost of higher cpu utilization:

How to increase maxdop just for synchronization purposes

AX 2012 has some index configuration options available in the System administration / Periodic / Database / SQL Administration form.

This would be the place to expect the option to increase MAXDOP, unfortunately this option is not available in standard AX (2012 RTM). Luckily it can be added by customizing the following objects.

Download the xpo here: SysSqlSetup_MaxDop

After importing the xpo, the table and index options screen has the additional MaxDop option

Enabled options in this form are used by the standard AX synchronization process. Next time when deploying a modelstore you should notice an improvement in the synchronization process (if any indexes were added or modified).

Dynamics AX 2012 RTM on SQL Server 2016

In case you were wondering whether AX 2012 RTM will run on SQL Server 2016, have a look at this video:

The AX kernel version used is 6.0.1108.8690. SQL Server 2016 was installed with all default options. The database was restored from a SQL Server 2008r2 AX database. This database has compatibility level 100.
While this seems to work fine, at the moment this is probably not a supported configuration.