Microsoft_MVP_banner

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

Share:

Related Posts

Microsoft Copilot
admin

Exploring Microsoft Copilot Architecture

Microsoft Copilot isn’t just another AI tool; it’s a comprehensive platform designed to be your indispensable companion, enhancing productivity, fostering creativity, and facilitating information comprehension all through a user-friendly chat interface. The concept of Copilot emerged two years ago when Microsoft introduced GitHub Copilot, aiming to assist developers in writing

Read More »
How to enable new Microsoft teams - Public Preview!
Microsoft Teams
Faisal Fareed

How to enable new Microsoft teams – Public Preview!

New Microsoft Teams is just AWESOME, quick but useful post below shows how you have this preview feature to make your life EASY!  Open Microsoft Teams admin center [Ask admin in your organization if you don’t have access] and follow path Teams > Teams update policies > Click on an existing

Read More »

Send Us A Message

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Post

Exploring Microsoft Copilot Architecture

Exploring Microsoft Copilot Architecture

Microsoft Copilot isn’t just another AI tool; it’s a comprehensive platform designed to be your indispensable companion, enhancing productivity, fostering creativity, and facilitating information comprehension all through a user-friendly chat interface. The concept of Copilot emerged two years ago when Microsoft introduced GitHub Copilot, aiming to assist developers in writing…

How to enable new Microsoft teams – Public Preview!

How to enable new Microsoft teams – Public Preview!

New Microsoft Teams is just AWESOME, quick but useful post below shows how you have this preview feature to make your life EASY!  Open Microsoft Teams admin center [Ask admin in your organization if you don’t have access] and follow path Teams > Teams update policies > Click on an existing…

Electronic Reporting: Send vendor payments to external azure storage via X++

Electronic Reporting: Send vendor payments to external azure storage via X++

Electronic Reporting module in Microsoft Dynamics 365 Finance Operation lets you archive file generated by ER at SharePoint location and in Azure Storage as per this link Archive ER destination type – Finance & Operations | Dynamics 365 | Microsoft Learn. APIs can be used to check message status and read…

FaisalFareed@2025. All rights reserved

Design by T3chDesigns