Slow loading sales forms and report timeouts

(0) Share

Report

Posted on by bankk

6,603

Good morning,

We've been having some issues over the last few days with performance issues in specific areas of AX. A few examples, S&M Delayed shipments, S&M Backorder lines, some Sales-related reports. The AX menus typically will load but can take up to 3 - 4 minutes, while the effected reports often will timeout. And to be clear, these have not been issues in the past. There appear to be no issues with server resources, they all seem to be running in the normal ranges.

Any suggestions would be appreciated.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Slow loading sales forms and report timeouts

You must capture the SQL Statement's Execution plan by running the process that you perceive as slow and then monitor the database execution of it (sp_axwho stored procedure on your AX Database, or SQL Profiler, etc.). Then capture it again when it is slow.

If you see that it times out, then you could improve your query plan by checking the underlying table indexes if they are correctly covered, or simplify the AX query/select statement to have less joins and avoid a Query plan execution timeout (like I explained on the blog a similar case: www.daxrunbase.com/.../improve-ax-performace-by-fixing-bad-query-plans)

If you see that there are different query plans running, you have to create an SQL Plan Guide for when it is running fast to "pin" that version, then your problem will never come back again with this problem.

As your AX database table changes over time, the statistics get out of date, and when you do a maintenance with full scan, it essentially forces SQL Server to recalculate the query execution plans, which you could avoid with a plan guide.

Was this reply helpful? Yes No
bankk 6,603 on at

Like (0)

Report

RE: Slow loading sales forms and report timeouts

Good morning,

The issue self-corrected last weekend but all of a sudden resurfaced this past weekend. No failure in the index maintenance this time, and the VMs look healthy as they did with the last occurrence.

Was this reply helpful? Yes No
André Arnaud de Cal... 299,024 Super User 2025 Season 2 on at

Like (0)

Report

RE: Slow loading sales forms and report timeouts

If the SQL maintenance plan fails, it could indeed slow down AX as indexes can get fragmented. If a newer job did not fail, the maintenance should be OK again.

Do you have virtualized machines? If so, check on the host if there are possible slow I/O measurements or over-allocating of hardware resources.

Was this reply helpful? Yes No
bankk 6,603 on at

Like (0)

Report

RE: Slow loading sales forms and report timeouts

Hajish, I have checked all those areas. Nothing there which sticks out as points of concern. Thank you.

Was this reply helpful? Yes No
Hajish 460 on at

Like (0)

Report

RE: Slow loading sales forms and report timeouts

Check log file and also tempdb size.

Make sure the db(data and log ) have enough space to grow in disk.

Was this reply helpful? Yes No
bankk 6,603 on at

Like (0)

Report

RE: Slow loading sales forms and report timeouts

Hello,

We do have a SQL plan for index maintenance. I'm not all that familiar with the details but I just realized that the job attached to that maintenance failed a few days ago. Could something like that have cause SQL to degrade over the last 5 days?

Thank you.

Was this reply helpful? Yes No
Suggested answer

Hajish 460 on at

Like (0)

Report

RE: Slow loading sales forms and report timeouts

I agree with above(reindexing proccess as Mr.Vilmos Kintera suggested ) and also check sql and ax server is in high performance power mode.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Slow loading sales forms and report timeouts

Do you have proper, regular SQL database index and statistics maintenance for your AX environment?

If the answer is no, then that is your problem.

If yes, then it could be a whole range of other things. Antivirus, IO or network bottleneck between AX, SQL and your storage, or a process which is running in the background that hogs your resources (large report printout, or financial postings).

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

Like (0)

Report

RE: Slow loading sales forms and report timeouts

If there was no problem with any resource, well, there wouldn't be any problem. You have to continue until you find the problem. Only then you can fix it.

I would check the database server (slow queries, waits etc.), trace one of the slow processes and analyze it in Trace Parser. I would expect that you have a problem with database queries to a certain table, but it's hard to guess. It's better to work with data than with guessing.

Was this reply helpful? Yes No