Sunday, April 13, 2014

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.


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


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.


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:



Stay Tuned for next part.

Thursday, April 10, 2014

CIL generation: Duplicate type name within an assembly.

Issue Definition:
I have been trying to generate a FULL CIL into our working live environment and suddenly it started giving me the following error;
                          CIL generation: Duplicate type name within an assembly.
Assessments:
I did the following steps:

1- Strong recommendation, apply latest kernel build. Download link:


Package:
-----------------------------------------------------------
KB Article Number (s) : 2957061
Language: All (Global)
Platform: i386
Location: ( http://hotfixv4.microsoft.com/Microsoft%20Dynamics%20AX%202012%20R2/nosp/KB2957061/6.2.1000.7176/free/474249_intl_i386_zip.exe )

2- To improve Compilation speed I did following changes :

- For the full compile not catching all the syntax errors, please follow the  X++ code workaround suggestion:

Under \Classes\SysCompilerOutput\startCompilation
Simply delete or out comment the marked line completely.



  
  
-For the CIL compiler performance:

-      Make sure that the “Client/Server trace” under the client options is unchecked.



-      Change the tier  from “client” to  “server” on \Classes\SysCompileIL\dumpParallelBulkn method.


- Further improvements is provided under: http://blogs.msdn.com/b/axtools/
3- For the full CIL generation failure:

  1. Stop all AX client sessions
  2. Stop all AOS services
  3. Take a copy of XPPIL folder
  4. Take a copy of Model database
  5. Delete content of XPPIL folder
  6. Truncate SYSXPPAssembly table from SQL backend
  7. Retsart AOS
  8. Restart AX client
  9. Complete full XPP compilation
  10. Completet full CIL generation
Happy Daxing!