UA-44032151-3 page contents

SQL Server 2016 CE and Dynamics AX 2012 R3


Hello, everyone!  As many of you are already aware, SQL Server's Cardinality Estimator received its first update in SQL Server 2014 (this was its first update since SQL Server 7.0), then again in SQL Server 2016.  Many customers on Dynamics AX 2012 R3 who have upgraded to SQL Server 2016 and have changed the compatibility mode to 130 have experienced performance degradation.  This is a result of the new Cardinality Estimator.  To resolve this issue, instead of changing the compatibility level down to 110 (SQL Server 2012), you can change several options at the database level while leaving the compatibility level at 130:

< set_options > ::=
{
MAXDOP = {<value>}
| LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}
| PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
| QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
}

Please note that in SQL Server 2016, MAXDOP is now a database-level setting.  Setting MAXDOP to 1 is still a recommended setting for Dynamics AX.

Skip to main content