Minor Changes in Database Configuration Checklist for Dynamics AX

We have made minor but important modifications to the Database Configuration settings, which are still posted under March, 2008 archive.  The archived posting includes the most current recommendations.  We’ve also posted a Microsoft Word version of the checklist below.

  • Changes specific to Microsoft Windows Server 2008 environments:

    • Max Server Memory for a SQL Server instance does not need to be managed with a custom setting, see KB918483 for details. (Configuration Part 1)

    • Partition alignment on SANs does not have to be performed manually on Windows Server 2008.  The default partition offset is 1024K which is suitable in most scenarios. (Configuration Part 2)

  • We have also modified our recommendations to set Auto Update Statistics Asynchronously to FALSE (from TRUE), which is the default setting.  If you modified this setting, please change it back to FALSE.  (Configuration Part 2)



Comments (6)

  1. Can you give us any information on why the recommendation for "Auto Update Statistics Asynchronously" has changed?

  2. davidre says:

    There is a particular data access pattern in AX that is negatively affected by this setting.  The pattern involves creation of a temporary dataset in a permanent table, a “delta” dataset, which is subsequently referenced in an update statement.  Examples of this pattern include InventUpdateOnHand and GLPost.  If the async setting is true, the “delta” table appears to the optimizer as if it has zero rows; if the async setting is false, it forces a synchronous stats update and the optimizer then bases its decision on correct cardinalities.  

    In other cases, any positive effect was negligible.  

    This is the reason for the change in our recommendation to set “auto update statistics asynchronously” FALSE.

  3. Nathan hintz says:

    I have seen these issues mentioned above when dealing with InventUpdateOnHand. Glad to hear of these configuration changes, the "Auto Update Statistics Asynchronously" eliminated the issues I’ve seen.

    <br />

    <br />

    <a href="http://www.perpetualdynamics.com/"&gt; http://www.perpetualdynamics.com </a>

  4. alex.bergdolt says:

    There was a bit confusion here regarding AX and read commited snapshot isolation (RCSI). I understood http://technet.microsoft.com/en-us/library/ms175095.aspx in a way that enabling RCSI means changing READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to “1”. Am I right?

    DatabaseConfigurationChecklist_V_1_2 only mentions the database setting READ_COMMITTED_SNAPSHOT that might lead to the conclusion RCSI = READ_COMMITTED_SNAPSHOT. Maybe you could clarify this topic?



  5. davidre says:

    Regarding the correct setting for Read Committed Snapshot Isolation (RCSI), we recommend running


    When you look at the entry for the database in sys.databases, you should see the following values:

    sys.databases.is_read_snapshot_on = 1

    sys.databases.snapshot_isolation_state = 0

    The value of snapshot_isolation_state is affected by the following command:


    Running both of these ALTER DATABASE statements will bring the AX database into conformance with our recommendations regarding RCSI and isolation level, and will result in the values shown above for these two columns in the sys.databases entry.

  6. apeltier says:

    Any update for AX 2012 running on SQL Server 2012 ?