Configuring the Microsoft Dynamics AX business database for better performance

Microsoft recommend the following settings for the Microsoft Dynamics AX business database. You can use SQL Server Management Studio or the appropriate ALTER DATABASE statement to configure these settings.

• Set COMPATIBILITY_LEVEL to 110 for SQL Server 2012, or to 100 for SQL Server 2008 or SQL Server 2008 R2.

• Set READ_COMMITTED_SNAPSHOT to on. Performance testing has shown that Microsoft Dynamics AX performs better when the READ_COMMITTED_SNAPSHOT isolation option is set to on. You must use an ALTER DATABASE statement to set this option. This option cannot be set by using SQL Server Management Studio.

  Run the following query, where <database name> is the name of the Microsoft Dynamics AX database. There can be no other active connections in the database when you run this query.

  ALTER DATABASE <database name>
      SET READ_COMMITTED_SNAPSHOT ON;

  
  

  Query the sys.databases catalog view, and verify that the Microsoft Dynamics AX database contains a value of 1 in the is_read_committed_snapshot_on column. For more information, see the following Web pages:

  
  
  
  
  • 
    

    sys.databases

    
    
  • 
    

    Choosing Row Versioning-based Isolation Levels

  
  
• 
  

  Set AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to on. Set AUTO_UPDATE_STATISTICS_ASYNC to off. Performance testing has shown that Microsoft Dynamics AX performs better when the options have these settings.

  
  
• 
  

  Make sure that the AUTO_SHRINK option is set to off. When database files are automatically shrunk, performance of the database degrades. We recommend that the database administrator manually shrink the database files on a predefined schedule. For more information, see Turn AUTO_SHRINK OFF! on the SQL Server Storage Engine Team's blog.

Important

All Microsoft Dynamics AX databases must use the same SQL collation. These databases include the business database, model store database, Microsoft SQL Server Reporting Services database, and Microsoft SQL Server Analysis Services database.