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):
CREATE INDEX I_359STATUSITEMIDX ON "DBO".SALESLINE (DATAAREAID,SALESSTATUS,ITEMID)
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:
CREATE INDEX I_359STATUSITEMIDX ON "DBO".SALESLINE (DATAAREAID,SALESSTATUS,ITEMID) WITH (MAXDOP = 4)
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).