Performance on AOS slow

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi Community,

last year my Company decided to replace our old ERP-System with Dynamics AX so with the beginning of this year we started using AX in our Finance Department.

Since the productive Start there was a feeling, that the AX-Environment slow, but it was only a feeling I could not measure.

As the records in the Database grew our batch jobs started taking longer and longer so I started to analyse the performance.

One of the jobs that is running slow is the Job "Process ledger transactions" in the Cost accounting module so I created a trace in the tracing cockpit while processing the transactions for one day to find the bottleneck.

In this trace I see that The overall process took approx. 26 seconds where the Database Time is less then 6 seconds.

Another process is the Creation of collection letters in the Accounts receivable module. When I run this job for one big customer the overall process takes approx. 28 seconds where the Database Time is approx. 8 seconds.

Having this information I believe the bottleneck is the code execution on AOS and not the Database server.

I disabled our Antivirus Software and have also tried to move all other virtual Machines to another ESX Host so the AOS-Server has one exclusive Host, but both did not improve the execution time.

Having a look to the CPU load during Job-Execution I can see that the VM is consuming approx. 20% CPU and the ESX host is not consuming more than 10% on any core.

Is anyone else experiencing such performance problems and what could be the reason for this Problem? - I can not believe that executing such simple code takes that long without consuming CPU.

Some Information how our System is configured:

Dynamics AX 2012 R3 CU9 with ISV Solution for our Business (There is no modification in the code-units mentioned above)
Production Environment with 2 AOS Server
Test Environment with 1 AOS Server
Each AOS-Server is running Windows Server 2012 R2 on a ESXi Cluster with 16 GB RAM and 4 Cores
SQL Server 2008 R2 SP2 as Hardware Cluster (1 Instance for each AX Environment with 32 GB-Ram exclusive allocated)
Currently 15 active Users.

The Trace for the both Jobs:

I hope someone can help me

Thanks and Regards

Henning

*This post is locked for comments

All responses (8)

Answers (1)

Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Performance on AOS slow

Hi,

I just want to inform you all that we found a solution for our Performance Problem.

The ESXi Cluster is build on HP Servers. On the ILO boards we found a setting called "Power Regulator" which by default is set to "Dynamic". With this Setting the HP Server uses the Power Settings configured on OS Site (which is set to "High Performance").

We have changed this Setting to "Static High Performance", which directly had an effect on the average power consumption of the Server (from 170 Watt up to 250 Watt).

Before and after changing the setting I ran the "Process ledger transactions" in the Cost accounting (for 1 legal entity and one month) several times and measured the average run time.

Before changing the setting the job ran 3.5 minutes afterwards it only ran 2 minutes. In a bigger legal entity the result was even bigger. There the time was reduced from 38 minutes to 18 minutes.

I now asked the users to give their feedback to the overall feeling, but with this test result I would say we found a solution and this topic can be marked as solved.

Thank you all again for your help.

Henning
Community Member Microsoft Employee on at

Like (1)

Report

RE: Performance on AOS slow

Hi,

I just want to keep you informed what I did the last week.

I have configured the TraceFlags, but this did not improve the performance.

I also executed the script posted by Vilmos. The result was the following:

TempDB: 1ms read, 5ms write

Data: 5ms read, 12ms write

Log: 0ms read, 0ms write (both < 0,00005ms)

So this is at least acceptable. But all files are on the same volume, so the read and write performance could be good for most of the queries (so the average is also good), but when running queries that use both the tempDB and the AX-DB the performance could go down.

I will use the dmv used in this script to get the read and write performance during running my test-jobs (Substract the values after running the job from the ones before).

Also our system administrator should show me the activity of the storage when I run the jobs.

And as a last step I will update the kernel version in our test environment.

I will be on vacation the next three weeks, so it will be july until I can post the results.

Thank you all again for your help
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
RE: Performance on AOS slow

Hi

Nice seeing that you went through the list, I hope it was a useful read.

For your storage performance do not believe what the system administrator says, check it for yourself. You should never keep together TempDB, AX Data file and log file on the same disk and IO path. You can mix TempDB log+data only.

Here is a script that you could run on the Production SQL instance to find out the average response times since the last time you have started up your SQL instance:

SELECT DB_NAME(database_id) AS 'Database_Name' , CASE WHEN file_id = 2 THEN 'Log' ELSE 'Data' END AS 'File_Type' , ((size_on_disk_bytes/1024)/1024.0) AS 'Size_On_Disk_in_MB' , io_stall_read_ms / num_of_reads AS 'Avg_Read_Transfer_in_Ms' , CASE WHEN file_id = 2 THEN CASE WHEN io_stall_read_ms / num_of_reads < 5 THEN 'Good' WHEN io_stall_read_ms / num_of_reads < 15 THEN 'Acceptable' ELSE 'Unacceptable' END ELSE CASE WHEN io_stall_read_ms / num_of_reads < 10 THEN 'Good' WHEN io_stall_read_ms / num_of_reads < 20 THEN 'Acceptable' ELSE 'Unacceptable' END END AS 'Average_Read_Performance' , io_stall_write_ms / num_of_writes AS 'Avg_Write_Transfer_in_Ms' , CASE WHEN file_id = 2 THEN CASE WHEN io_stall_write_ms / num_of_writes < 5 THEN 'Good' WHEN io_stall_write_ms / num_of_writes < 15 THEN 'Acceptable' ELSE 'Unacceptable' END ELSE CASE WHEN io_stall_write_ms / num_of_writes < 10 THEN 'Good' WHEN io_stall_write_ms / num_of_writes < 20 THEN 'Acceptable' ELSE 'Unacceptable' END END AS 'Average_Write_Performance' FROM sys.dm_io_virtual_file_stats(null,null) WHERE num_of_reads > 0

Our Production AX DB is reading 12 ms writing 4 ms (due to LUNs and RAID configuration), TempDB is reading 1 ms writing 15 ms, so gets good/acceptable rankings. I'd say everything above 20 ms probably needs your attention.

Please note that setting the Trace flags in the SQL configuration as startup parameters is required, and will only be active once your SQL instance has been restarted, so plan a proper outage for this if you have not done it yet.

Other things I could think of is to check in the AX Server Configuration if you have a too large Maximum buffer size setting. Default is 48 (it is specified in kilobytes) as far as I remember, my R3 test environment is set at 100. There could be some reasons why this value would be larger ie. large filter criteria in select statements, or long memo fields, but this can also have a negative impact on performance.

If storage and setup are all verified, then your only option now is to trace and troubleshoot the statement and the query execution plan that runs within SQL Server. Large joins, no covering index, AX recordset buffer operations, direct SQL statements that are not using parametrization are all suspect to slowness. And again the index/statistics maintenance can purge your query plans and if storage is slow so it cannot calculate the fastest plan, the SQL engine will choose a sub-optimal query and keeps timing out, which does result in bad percieved AX performance.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Performance on AOS slow

We upgraded to CU10 since in CU09 we could not clean up the inventory transactions. Our inventory on-hand became extremely slow after 1 month.
Community Member Microsoft Employee on at

Like (0)

Report
RE: Performance on AOS slow

Hi,

I reviewed the points mentioned in your posts and checked our configuration (summary below). I could find 3 points that require more investigation. These are:

Check activity on SAN Volume of SQL Server

Patch kernel to current version

Set SQL trace flags

I will post the result here.
Regards
Henning

Here is the summary of everything I checked:

Power Management:
This is set to high performance for all servers
⇒ Fine

TempDB is slow:
The SQL-Server is currently configured to have one clustered volume per SQL Instance, so we only have one Volume that is used for the datafile, the logfile and the tempDB. For a better performance it is recommended to have 3 separate volumes for this. Our System Administrator told me he reviewed the activity on the one volume we have and he said it is “nearly sleeping”. I will again talk to him to review the activity while running the two test-jobs.
⇒ Check activity on SAN Volume

The TempDB has 8 data files with an initial size of 4 GB and auto-growth set to 1GB. As mentioned above the TempDB does not have its own volume, so I cannot pre-allocate the total volume size, but I think those settings are ok.
⇒ Fine

Caching:
I checked the caching setting for all tables shown in the trace and all of them have the caching set to None or NotInTTS except some smaller master data tables like MainAccount (all <300 records)
⇒ Fine

Top 10 issues discovered from Dynamics AX Health Check:

MaxDOP is set to 1 for the AX instance
⇒ Fine

Number Sequences Pre-Allocation:
⇒ Both jobs mentioned do not consume many IDs, so I think this cannot be the problem.

Auto Growth: The initial size for the datafile was set to 50GB, for the logfile to 15GB and auto-growth is set to 500MB for both files. Both files still have their initial size
⇒ Fine

Batch Processing threads: The Batch-Threads are set to the default value of 8, but I am not running the Job as batch, so I think I do not have to look for this.
⇒ Not applicable

Application database logging: This feature is not used currently, SYSDATABASELOG table is empty
⇒ Fine

Missing Indexes: I have not looked for some missing indexes yet, but none of the called tables in the both jobs are mentioned in the missing_index DMVs
⇒ I think not the problem (for this jobs)

Wrong index maintenance: Every night we run a reorganize or rebuild depending on the index fragmentation (<5% do nothing, 5-30% reorganize, >30% rebuild with 90% fill factor) and update the statistics after this step
⇒ Fine

Debugging: Debugging is not enabled
⇒ Fine

Kernel and Application are outdated: We are running R3 CU9 (6.3.2000.323) as application and 6.3.2000.3321 as kernel version. We could upgrade the Kernel to the current CU10 Release, but it is a lot of work to update the kernel on every client. I will try to patch the Test-environment to the latest Kernel and can then decide to update the productive environment.
⇒ Patch Test-Version to current kernel

Index Fill-Factor:
The fill factor is set to 90% (as mentioned in the index maintenance task above)
⇒ Fine

SQL Trace Flags:
Currently no Trace Flag is set. I reviewed the function of all Trace Flags mentioned in the PFE blog and decided that those mentioned in the “AX database tuning” (1117, 1118, 1224, 2371, 4199) are also good for us.
⇒ Set Trace Flags

Parameter Sniffing:
The dataAreaIdLiteral AX setting is set to 1 in our environment.
⇒ Fine
Community Member Microsoft Employee on at

Like (0)

Report

RE: Performance on AOS slow

I thought I already added an answer to this Thread this morning, but it seems I was just dreaming...

Thank you both for your posts.

Today I had a quick view to the information you posted and had a first review to our configuration.

At first view our environment seems to be configured correct, but I will have a deeper look tomorrow and give you a summary what I checked and what the results where.

Thanks and Regards

Henning
Suggested answer

Vilmos Kintera 46,149 on at

Like (1)

Report

RE: Performance on AOS slow

Here is a comprehensive list of things to check to see if your AX is healthy.

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

All the linked articles are relevant and are mostly from Microsoft Dynamics AX Premier Field Engineers.

Please establish that the configuration and setup of your environment is adequate and by the best practices listed in those articles. I am pretty sure that there which you are missing (index/stats maintenance? TempDB contention? memory pressure?)
André Arnaud de Cal... 291,820 Super User 2024 Season 2 on at

Like (0)

Report

RE: Performance on AOS slow

HI Henning,

First check if the machines are not running in power save mode.

If there is 28 seconds processing time and 8 seconds database, a large part could be related to caching or using the TempDB. If the TempDB is slow, the Trace parser will blame the AOS. Unfortunately the screenshot is not interactive to be able to dig a bit further....

Several times we changed the caching property to gain more performance. Example: An entire table cache caused 50 seconds delay which was resolved by changing it to None or NotInTTS. This table had about 26000 records.