Slow AX application but low activity on SQL server

(0) Share

Report

Posted on by Community Member

Hi all -

I'm experiencing issues with a sporadically slow AX system. I am unable to replicate the issue so I understand this is limited information in order to request help for you guys. Selecting to change country, open journals, all sales orders the system can hang for 2/3 minutes. Just general browsing around the application. Different users, different times produces different results. Sometimes it's fast.

All the while the SQL db is hovering around 3 or 4 % CPU, 0 waiting tasks, 0.5 I/O and circa 10 batch requests.

End user machines i5 8GB.

I accept asking for help with this one is a challenge but thought it was worth at least mentioning,

Many thanks in advance

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

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

Like (0)

Report

Why do you think it's about SQL Server and not, say, a network issue?

Was this reply helpful? Yes No
Suggested answer

Pravasti AK 2,985 on at

Like (0)

Report

Hi Dano,

is SQL configured correctly

check for missing indexes.

Check if there are customizations on the startup AX classes.

Stop AOSs, backup the SQL tables SysServerSessions and SysClientSessions, delete all records from the above tables, Start Aos

On SysClientSessions table, AX store all the Inactive sessions ,these records impact also on Licenses and on Aos startup time

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Hi Martin - I don't think it's SQL. SQL is happy (appears to be) but still really slow (at times) Yep - possibly network.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Kavitha - I'll take a look at doing the above. If tables are large would that be a sign of an issue?

Was this reply helpful? Yes No
RM-22040759-0 2,014 on at

Like (0)

Report

Hi Dano,

Initiate continues ping from

1. AOS server to SQL,

2. AX Client machine to AOS server,

3. AX Client machine to SQL server and check is there any network drop/ network latency is more?

Best Regards,

Ravi Theja Madisetty

https://technoguns.com

Was this reply helpful? Yes No
Suggested answer

guk1964 10,888 on at

Like (0)

Report

There can be many reason e.g. wrong Raid, slow hard disks, poor server architecture, inappropriate SQL e.g. for settings such as maxdop, trace flags set, , how many temp dbs and sizes, no sql maintenance scripts for reindex or statistics build, or overfull log files, or auto windows/office updates on your server, or anti virus running on system files, inappropriate allocation of system resources, or inappropriate power settings, or just unusual data loads, or non optimised code or reports etc. Its really much too wide and deep a topic to cover in such a forum unless you can narrow it down.

Was this reply helpful? Yes No
Konrad U 804 on at

Like (0)

Report

If the clients are on a decent machine and the DB is under no stress it is probably due to issues with the AOS, either queuing, remote access or lack of resources. One suggestion might be to create another AOS and attach a couple of users to it to see if they get the slowdown.

Further debugging of the SQL sever machine could include looking at the sessions attached to the database to see if one session is blocking the others bringing everything to a halt.

Finally if they are modifications they should be reviewed to make sure the tables are properly indexed to avoid spikes in SQL server requests and wait times.

Was this reply helpful? Yes No
Suggested answer

Acer 75 on at

Like (0)

Report

You are most likely impacted by parameter sniffing, bad indexes, bad query plan, bad statistics (which causes bad plans) - or you have people pulling a "sh*t load" of data from your database via linked servers or similar. The latter will completely f*ck up your performance. Don't ever let people do that. Not even if they are so-called "experts"

If I were you I would try to see if I could find a time where you are the only you online and then try to reproduce the slowness.
Install Brent Ozars scripts on the database and run sp_BlitzWho to see what is going on.
Check the query plan. Try to drop the plan and run it again at different set of data (if you were looking for data for customer xxx - then try yyy)

If you have bad statistics on the table / tables you may experience an odd query plan.

Since AX has most of its indexes start with DataAreaId - you have pretty bad statistics for your indexes (they are all the same since it only looks at the first field) - so for instance when it tries to join with InventDim - and it brings in InventDimId, and PalletId - it may be using PalletId as the "primary" index for the lookup...

I have solved most of our problems with either adding a "query plan" (as a quick fix), index tuning - or completely changing the code (altered the query).

Was this reply helpful? Yes No
Suggested answer

Hossein.K 6,648 on at

Like (0)

Report

Hi Dano,

First of all I propose you lunch a client of AX in the same server which ran SQL server. If the issue is exist and still , check you solution related configuration that some else said.

But if in this case solved, check configuration of network or ...

Was this reply helpful? Yes No