Hardware
Some companies invest well in good hardware and others just run on the bare minimum so it’s a good place to start.
The SQL server should be running on dedicated hardware, solid state disks / PCIe cards, and as usually recommended the database log and data files should be on separate drives if possible. SQL likes to cache everything that it can into memory so at least 32GB of ram on the SQL box.
AOS servers mostly consume cpu for processing the business logic, so a multicore CPU with high clock speed and at least 16GB of ram
A fast network connection between the AOS servers and SQL database
Service packs & cumulative updates
It’s a time consuming and expensive process to apply an AX service pack, all machines running AX need to have their kernel updated and also a code merge needs to be done to merge code from objects that have changed in the service pack with customized code, then the whole thing needs to be tested. Many times I have seen where the code merge has not been done properly, although it does not always cause immediate problems it can make doing future upgrades more difficult.
Applying service packs can fix performance problems with the client / aos. Kernel fixes can resolve crashing and memory issues and application fixes sometimes contain performance improvements to posting and other processes.
SQL server service packs can also provide some performance improvements. By default hotfixes that improve SQL server query optimizer are disabled. To enable the enhancements you need to enable TraceFlag 4199 on SQL server.
Some other handy traceflags to enable for AX are
2371: Updates statistics more frequently
1224: Prevents defaulting from row locking to page locking based on row count. SQL will only escalate the locking if it is experiencing memory pressure
7646: Prevents blocking when using full text indexing
Traceflags can be enabled in the SQL server configuration, add the startup flags:
-T4199 -T2371 -T1224 -T7646
It may also be an idea to upgrade your SQL server version however keep in mind that if you upgrade the version of SQL server then you will not be able to restore any database backups made with the new version to an older version of SQL server.
Fixing customized code & poor design practices
There is a whole range of things that can go wrong here. Usually it’s just a matter of having a developer look over the slow areas of the system.
Some common problems are:
Missing indexes
Not using table caching
Poor architecture of tables and data
Queries nested within other queries
Using record by record updates where bulk updates could be used
Not using display method caching in forms
Too much client/server communication. – Running logic on client that executes many sql statements
Not running cpu intensive logic in CIL
Long string fields in tables rather than using memo (ie. 1000 chars)
There is a few tools that can help with diagnosing performance issues such as AX perf, tracing long running queries and AX trace parser.
Archiving old data
For companies with many transactions and many years’ worth of data, sql can become slow as it becomes more work for SQL server to use and maintain the table indexes. There are a few tools that allow you to export old transactions into an “Archive” instance of AX. The archived data is purged from the production instance of ax and can improve performance on transaction tables.
There is also a few batch jobs in AX that can clean up the sales update tables and old orders.
Turn off factboxes
One of Microsofts recommendations for slow client performance is to turn off the fact boxes and preview panes for the user.
Turn off dubugging
In your production make sure debugging is disabled on the AOS, this will give about a 20% performance boost.
These tips are just what I can think of but if I have left anything out then let me know.
