>

Tag Archives: Performance

Regular maintenance activities in Dynamics AX that affect performance

The following list describes some of the maintenance activities that we recommend that you perform regularly in your production environment:
  • Defragment indexes – You can defragment indexes from either SQL Server Management Studio or the Intelligent Data Management Framework (IDMF).

  • Update SQL Server statistics from SQL Server Management Studio – We recommend that you run both manual and automatic updates of statistics. Manual updates may become more important as the size of your database increases, because automatic updates are less likely to be completed on large data sets.

  • Reduce the size of the database – You can use IDMF to keep the size of the production database small. A small database makes database operations more efficient. For example, you can delete or archive data that is not required in your production system.

Regular maintenance activities in Dynamics AX that affect performance

The following list describes some of the maintenance activities that we recommend that you perform regularly in your production environment:
  • Defragment indexes – You can defragment indexes from either SQL Server Management Studio or the Intelligent Data Management Framework (IDMF).

  • Update SQL Server statistics from SQL Server Management Studio – We recommend that you run both manual and automatic updates of statistics. Manual updates may become more important as the size of your database increases, because automatic updates are less likely to be completed on large data sets.

  • Reduce the size of the database – You can use IDMF to keep the size of the production database small. A small database makes database operations more efficient. For example, you can delete or archive data that is not required in your production system.

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:

  • 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.

ImportantImportant

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.

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers:

error: Content is protected !!