Share via

Minor Changes in Database Configuration Checklist for Dynamics AX

  • Article

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)

 

DatabaseConfigurationChecklist_V_1_2.docx

Comments

  • Anonymous
    June 24, 2009
    Can you give us any information on why the recommendation for "Auto Update Statistics Asynchronously" has changed?

  • Anonymous
    June 25, 2009
    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.

  • Anonymous
    August 05, 2009
    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/"> www.perpetualdynamics.com </a>

  • Anonymous
    January 28, 2010
    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? thanks alex

  • Anonymous
    February 01, 2010
    Regarding the correct setting for Read Committed Snapshot Isolation (RCSI), we recommend runningALTER DATABASE <db> SET READ_COMMITTED_SNAPSHOT ON;When you look at the entry for the database in sys.databases, you should see the following values:sys.databases.is_read_snapshot_on = 1sys.databases.snapshot_isolation_state = 0The value of snapshot_isolation_state is affected by the following command:ALTER DATABASE <db> SET ALLOW_SNAPSHOT_ISOLATION OFF;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.

  • Anonymous
    August 29, 2012
    Any update for AX 2012 running on SQL Server 2012 ?