Microsoft Dynamics AX (Archived)

Performance tuning

(0) Share

Report

Posted on by @rp@n

Dear All,

For performance tuning we consider always

1. Use proper indexing

2. Instead of multiple while select use join

3. Use required field with select statement

4. Use SET based comment like Insert_INTO.

5. Use ttsbegin and ttsCommit

Could you please let me know any other things which we need to consider for performance tuning?

Thanks!

Arpan Sen

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (2)

@rp@n 30 on at

Like (0)

Report

Hi All,

Could you please any one help me on this.

Thanks!

Arpan Sen

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 300,915 Super User 2025 Season 2 on at

Like (0)

Report

Hi Arpan,

Monitor hardware resources and ensure there is an SQL maintenance plan where indexes will be defragmented.

ttsbegin/ttscommit is required for a database write. The commit size (number of records) has a relation with performance.

When possible use update_recordset or delete_from in stead of loops. For inserts you can consider using the insertRecordList. I assume you meant this with "Insert_INTO".

When you want to start a loop based on a variable, check if the variable has a value. Otherwise it might result in a full table loop.

Try to use as much as possible Dict-classes instead of SysDictClasses.

Some calls like isConfigurationKey enabled in a loop can cause long durations. It can be prevented by checking if there is a certain record or value filled or not which can be only filled when the config key has been enabled.

Use the correct cache settings on a table. Entire table cache on a transaction table is killing!

In addition you can use the TraceParser to find out about expensive calls.

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Thanks a lot Andre

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,805 Most Valuable Professional on at

Like (0)

Report

It's looks more like a list of best practices for developers then for an actual performance tuning (which happens later, when you run into performance problems).

Regarding tuning, the first rule is: measure! Guessing what's responsible for the problem is rarely successful; you need measurements to see what's slow and whether you tuning made a difference.

Regarding your list, it addresses just one of the major areas of performance problems: database queries. The other is cross-tier communication, such as when a client function calls AOS 100 times - if the round-trip latency is 10ms, a whole second was just wasted without any reason. This kind of problem is very common, because people often don't pay any attention to it.

I would also add caching to your list and remove transaction handling (it's important, and excessive locking/rollbacks have performance implications, but you don't address the problems just by saying "use ttsbegin").

Was this reply helpful? Yes No
Martin Dráb 237,805 Most Valuable Professional on at

Like (0)

Report

André, what's the problem with SysDict* classes? I've never heard of anything (and didn't notice any problem in my code), much less that it's one of the most important things to take into account when dealing with performance.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

Performance of correctly written code does help, but majority of the improvements would come how you have actually set up your system and how healthy is your database.

You should check the posts of the AX Performance Team and suggestions from the Microsoft Premiere Field Engineers based on customer experience. I have collected some of those links and my suggestions on my blog under this post:

www.daxrunbase.com/.../ax-database-tuning-and-maintenance

Also for the best performance your hardware should only serve AX and its' components, and not running any other system components (SharePoint, other ERP components or third-party applications), including VM hosts, networking and storage system.

To further improve performance, you need to make sure you have all the applicable hotfixes applied. They greatly help to boost SQL Server performance in many cases due to better query optimization, or how the internal works of SQL is changed (memory handling, writing to disk, threading, etc.)

You can also improve system performance by moving out some tasks from AX, like reporting. We are using an SQL Server AlwaysOn cluster, and use external SSRS/PowerBI reports that rely on static data only (no computed columns or business logic from AX). That way we could run those reports off from the secondary database replicas, without slowing down the actual live system (primary SQL instance).

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Hi Martin,

Could you please let me know the reason of improving good performance with using of SysDict class.

Thanks!

Arpan Sen

Was this reply helpful? Yes No
Martin Dráb 237,805 Most Valuable Professional on at

Like (0)

Report

I can't. If you read my reply, you'll see that I said that I'm not aware of any performance implication. It's up to André.

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Hi Andre,

Could you please explain me "Try to use as much as possible Dict-classes instead of SysDictClasses" in details. Why not to use SysDictClass?

Thanks!

Arpan Sen

Was this reply helpful? Yes No
Suggested answer

guk1964 10,888 on at

Like (0)

Report

There are many things to consider starting with what version of Ax and SQL and what hardware - the right hardware eg correct RAID, spindles, Power setting, location of logs and operating system on separate drives from application, not running automatic office updates on the production server, not anti virus checking Ax, Windows Server patching, use of flash drives for operating system etc. Whether load balanced AOS are set up.

Then the database RCSI , traceflags, temporary database set up, management of database growth, sql maintenance plans - sql statistics is generally much more important than indexing in Ax. Back up method.

Management of windows and sql logs.

Are unnecessary modules configured.?

Workflow timeout settings, not having test systems in productjon,. Remove unwanted localisation indexes on major tables,

Where do users experience problems? What is the volume of transactions? Number of users/ DO run retail -how many pos? Are the settings optimum for the number or pos

There are many configurations and pieces of code that perform differently under different configurations, filters or data volumes. Are inventory closes and month end up to date. Do you run mrp?

Do you have adds on, multiple reports, or customisations, or integrations or localisation requirements or multi company processes?

An enterprise system like this Is rather too big a subject for self help, unless its just one specific issue you are facing, An experienced partner should be able to help you to check probable known issues, or those that factors that should be considered when coding for your environment with a 2-3 day audit. Much of which can probably be done remotely. .

Your intention to avoid coding errors is commendable but I agree, you need to measure also -test systems do not perform the same as production systems..

Was this reply helpful? Yes No