Tune database settings in ax 2012

Hi,

Tune database settings in ax 2012

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:

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

2.     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, search for the following topics in the Microsoft Dynamics AX Help: Concurrency model configuration (Form)

1 Transaction integrity

2 Exception handling

3 Select statement syntax

4 Table properties

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 size value.
Results for ad hoc queries are returned slowly.	Check to see that the appropriate indexes are in place.
Transactions are failing frequently.	Decrease the Transaction retry interval value.
Data grids for commonly used tables draw slowly.	Increase the Array fetch ahead value.

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. 1. Parameters allow Microsoft Dynamics AX and the database server to reuse the query when search values change. They are preferred for high-frequency queries. 2 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

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++. Adjust the use of autogenerated string functions Microsoft Dynamics AX embeds some string functions in SELECT statements automatically. String functions are included to support: 1.Treating uppercase and lowercase versions of the same text as the same text (single case) for Oracle installations. 2. 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 INDEX hints and are running more slowly than expected, clear the Allow INDEX hints in queries option. 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: 1.The OPTION (FAST), LOOP, and FORCE ORDER hints are not applied by default, but are applied if explicitly specified in X++. 2. A FIRSTONLY hint in X++ is translated into the addition of a TOP 1 statement to the SQL Server query. 3. FASTFORWARD cursors are used for all user queries unless a cursor has been marked as FOR UPDATE. 4.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.  Reference