Dynamics AX 2012 - Advanced Performance Optimization - Part 1

To maintain the performance of an application with large amount of data is always being a night dream for any organization. However, things can be control with pro-active steps and can easily be managed if something happen other than expectation.

This blog entry is mainly focused on some of the (there can never be 100%) points which we can take into our consideration while implementing and customizing Dynamics AX 2012.

Dynamics AX AOS settings

Index Hints and LTRIM

The best practice is to disable index hints allowing SQL Server to decide the most efficient way to process queries instead of forcing it to use a certain set of indexes pre-determined in code. All performance testing for Microsoft Dynamics AX 2012 was done with index hints disabled. If index hints are being used to address specific performance issues, it’s better to address those situations individually rather than globally enabling index hints at the AOS.

It is also best practice to disable LTRIM. The use of functions such as ltrim in a where clause can significantly degrade query performance by requiring scans to be used instead of seeks. LTRIM was mostly used in Microsoft Dynamics AX 3.0 to address issues with the right-justification of data. Since data should all be left-justified in Microsoft Dynamics AX 2012 it should no longer be used.

Both ‘Index Hints’ and ‘LTRIM’ are controlled by registry key ‘hint’ with the following description:

Hint Registry Value	Description
Blank	Index hints enabled, LTRIM disabled
0	Index hints and LTRIM disabled
1	Index hints enabled, LTRIM disabled
2	Index hints disabled, LTRIM enabled
3	Index hints and LTRIM enabled

The value ‘0’ (Index hints and LTRIM disabled) is the recommended value. 

To change or check this go Start/Run and type regedit and go to “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\DynamicsServer\6.0\[AOS instance number]\[AOS configuration]\hint”

and change the value to 0.

Maximum buffer size

The maximum buffer size determines the maximum amount of data that the AOS can retrieve from the database per database call. A larger buffer allows more rows to be retrieved per database call. The default value is 24 KB. 

Note: In AX 2012 R2 this default value is now 48KB.

Changes to this setting should be done carefully. Adjust this value when queries that return a large number or rows are executing slowly. Increase the maximum buffer size value in small increments. If this adjustment has worked, the number of round trips to the database, as measured in Performance Monitor by the SQL Server statistics: batchrequestsPerSecond value should decrease. Stop increasing the value when the rate of improvement starts to diminish. For more information about how to tune data access, see Tune data access settings.

This setting can be changed using the Microsoft Dynamics AX Server Configuration Utility > Database Tuning tab > Settings

Statement cache

By default the “Statement cache” setting is on AX 2012 RTM is 256 (In AX 2012 R2 this value is 450 by default). The recommendation is to leave this value as default value. A value that is larger than default can cause memory issues on the server. If you must increase the value in this field, validate that the value is appropriate, based on your data composition and query patterns. For more information about how to tune data access, see Tune data access settings. 

This setting can be changed using the Microsoft Dynamics AX Server Configuration Utility > Database Tuning tab > Settings

See also http://technet.microsoft.com/EN-US/library/aa569635.aspx

Debug Settings

In Microsoft Dynamics AX 2012 there are the following options in the Microsoft Dynamics AX Server Configuration Utility:

·         “Enable breakpoints to debug X++ code running on this server”  (xppdebug)

·         “Enable global breakpoints”  (globalbreakpoints) 

These settings enable breakpoints (pausing of code execution). There are two reasons why this is not recommended in a production environment.

1. There is significant overhead associated with enabling this feature, so performance is degraded even when no debugging activity is taking place.
2. The system becomes vulnerable to a user setting a breakpoint and pausing code execution. Depending on where the breakpoint is set, it might result in a SQL transaction being held open for an extended period of time. That may in turn cause blocking within the database that affects the performance of the application for other users.

These settings should be disabled in Microsoft Dynamics AX production environments. To disable go to Microsoft Dynamics AX Server Configuration Utility > Application Object Server tab > Settings and disable “Enable breakpoints to debug X++ code running on this server” and “Enable global breakpoints” for each AOS server.

Use of literals

The default is to have both settings turned off (unless there is a clear performance analysis done which recommends having one or both settings activated).

sqlcomplexliterals	Use literals in complex joins from X++	Specify that Microsoft Dynamics AX use literals rather than parameters for complex joins to optimize performance.
sqlformliterals	Use literals in join queries from forms and reports	Specify that Microsoft Dynamics AX use literals rather than parameters in long-running queries to optimize performance.

These settings can only be found in the registry. For more information please read:

http://technet.microsoft.com/EN-US/library/aa569637.aspx

Stay Tuned for next part.