You may want to tune the database settings for Microsoft Dynamics AX to improve performance. Before changing settings, trace the usage of your Microsoft Dynamics AX database to ensure that you have clear understanding of performance under the current settings. To trace Microsoft Dynamics AX database performance, use:

  • Tracing from the Microsoft Dynamics AX Server Configuration Utility. For more information, see Set tracing options.

  • Windows Performance Monitor, using Microsoft Dynamics AX Object counters.

Test all tuning changes before implementing them in a production environment. In a test or development environment, make a single change and then test your system's performance before making another change.

Change the concurrency mode

Concurrency settings enable you to reduce locking conflicts on your system. For more information, see the following topics:

Tune connections

The following table lists common connection issues, and some adjustments to try in the Server Configuration Utility.

Symptom

Adjustments to try

Results for common queries are returned slowly.

Increase the Maximum buffer sizevalue.

Results for ad hoc queries are returned slowly.

Check to see that the appropriate indexes are in place. For the most recent guidance about indexing, check Microsoft Dynamics AX Online.

Transactions are failing frequently.

Decrease the Transaction retry intervalvalue.

Data grids for commonly used tables draw slowly.

Increase the Array fetch aheadvalue.

Tune queries

If queries in the system are running slowly, you may want to change settings for literals, string functions, or hints. Microsoft Dynamics AX no longer uses literals by default in form and report queries, or in complex-join queries.

Adjust the use of literals

Microsoft Dynamics AX may pass either parameters (placeholders) or literals (actual values) in queries.

  • Parameters allow Microsoft Dynamics AX and the database server to reuse the query when search values change. They are preferred for high-frequency queries.

  • Literals allow the database server to optimize the query for a specific piece of information. This provides an optimal query for that piece of information, but the database server must perform the optimization for every query executed. Literals may be used for long running queries such as complex joins.

A developer can override the default use of literals by specifying parameters in their code, or an administrator can override the use of literals in the Server Configuration Utility.

Symptom

Adjustments to try

Anticipated effect

Long-running queries run slowly.

Review the query plan statements sent to SQL Server and consider taking corrective action. Using literals may be one solution.

Select Use literals in join queries from forms and reports.

Select Use literals in complex joins from X++.

Long-running queries pass literals to the database. Processing time for long-running queries should go down.

Adjust the use of autogenerated string functions

Microsoft Dynamics AX embeds some string functions in SELECTstatements automatically. String functions are included to support:

  • Treating uppercase and lowercase versions of the same text as the same text (single case) for Oracle installations.

  • Left justification or right justification.

When a string function is included in a query, the optimizer may have to choose a less-than-optimal access plan, such as a table scan, for retrieving data. If customers do not require the use of mixed case outside Microsoft Dynamics AX and do not use left justification or right justification, these functions are not required and should be turned off. To improve performance, we recommend that all values be stored left-aligned by default.

Adjust the use of hints

In Microsoft Dynamics AX, you can allow developers to override the index selected by the query optimizer. In most situations, allowing the query optimizer to select an index for a query results in improved performance.

If queries include INDEXhints and are running more slowly than expected, clear the Allow INDEX hints in queriesoption.

Changes in the use of hints

If you have upgraded to Microsoft Dynamics AX, the queries in your system may contain outdated Microsoft SQL Server hints. Configuration commands are no longer available to globally enable or disable many of the hints from previous versions. If hints are explicitly specified in an X++ statement, they are added to the SQL Server query that is generated. Otherwise, they are not added.

The following changes have also been made:

  • The OPTION (FAST), LOOP, and FORCE ORDERhints are not applied by default, but are applied if explicitly specified in X++.

  • A FIRSTONLYhint in X++ is translated into the addition of a TOP 1statement to the SQL Server query.

  • FASTFORWARDcursors are used for all user queries unless a cursor has been marked as FOR UPDATE.

  • FOR UPDATE, NOLOCK, and READPAST, hints are added to statements depending on the type of the cursor that an X++ query has produced. No interface is available to modify these hints.

See Also