Microsoft Dynamics AX (Archived)

Performance Issue

(0) Share

Report

Posted on by Lifeisbelieveful

363

Hello, Experts of AX.

I have been facing severe performance Issue from AX 2012 R2.

I have 2 environments: Live and Test.

When I open a form from Live environment, I could check "FETCH OPEN API Cursor 0000...." in SQL, and it makes the function slower.

I also did with the same form in Test environment, but I couldn't see the query.

Is it parameter sniffing something...?

I am really struggling this issue. I do not have idea about what "FETCH open API" is and why it occurs only Live environment, even though the both environment code and data is exactly same.

Please give me a favor about it.

Thanks.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (2)

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

Like (0)

Report

AX uses cursors a lot; it doesn't mean anything. You seem to be looking at a wrong thing.

What you say so far all doesn't allow to say whether it has anything to do with parameter sniffing, so I think you're just guessing. Stop guessing and rather collect real data. If you believe it's caused by a slow query, the first step is finding the query. Then look at its execution plan.

If you don't have any idea what's slow, then make a step back and collect information about what takes what amount of time. The trace parser might be very beneficial for that.

Was this reply helpful? Yes No
Malcolm Burtt 1,475 on at

Like (0)

Report

You don't say under what circumstances you see the cursor statement so its impossible to say why you "only see it on live" but AX uses cursors all across the application. You'll find that the actual SQL statement that the cursor is running is present in the SQL stats that I suspect you are looking at but its not easy to locate it. As Martin says, you should probably be looking at using TraceParser to do a detailed review of the process that you have a problem with.

Was this reply helpful? Yes No
Lifeisbelieveful 363 on at

Like (0)

Report

Thanks, Martin and Malcolm.

When I collect some data with Trace parser, only "servernext" has large amount of exclusive time, there is no sql statement which consumes much resources.

Could you please kindly explain what the servernext means? there is brief comment for servernext in trace parser and AX blogs, but I still cannot understand what it means.

Was this reply helpful? Yes No
Suggested answer

guk1964 10,888 on at

Like (0)

Report

ServerNext means the client executes a SQL query and gets the next batch of results - it doesn't provide any details because it represents a jump to the AOS where the execution continued. Copy ServerNext line in the client trace and paste it into the server trace to jump to the associated call in the server trace to track down the long running event which is probably a SQL statement.

Was this reply helpful? Yes No
Lifeisbelieveful 363 on at

Like (0)

Report

Thanks, Magic1949

I'd like to see the detail operation of servernext by copying the node to server, but I'm already checking in server side.

The servernext has only one sub-node, and it has very low exclusive time, so I don't think that query is not slow.

It is severnext only that is slow and has large amount of exclusive time.

For your quick reference, I attached trace parser result as below. how should I figure out this situation?

Thanks.

Was this reply helpful? Yes No
guk1964 10,888 on at

Like (0)

Report

Exclusive duration includes only the time spent by the code that executed on that line.

Inclusive duration includes the time spent at the current level plus all time accumulated by code called from this level

In your case there is very little difference and both are large so it looks like you have found where the problem lies.

You should be able to right click and drill to the client.

The SQL tab will only have data when you are reviewing a server trace. The AX client always uses an AOS as a proxy for executing database calls.

The X++/RPC tab: will show you which classes/methods are consuming the most time in the trace

Was this reply helpful? Yes No
Lifeisbelieveful 363 on at

Like (0)

Report

Thanks, magic1949

Thanks for your kind explanation, I'm getting point of trace parser.

Then, is it okay to understand that "servernext" means that get from client request and the large amount of exclusive and inclusive time stem from many client, correct?

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 301,146 Super User 2025 Season 2 on at

Like (0)

Report

Hi Daniel,

This duration should be explained by SQL statements or x++ coding. If there is no explanation, the remaining time is mostly spent by the AOS for e.g. caching of data which includes managing temporary tables. Try to find the source code and check if caching might be causing the issue or a very large data set in an in memory temporary table. I have seen issues in the past by having an entire table cache enabled on a transaction table.

Was this reply helpful? Yes No
Lifeisbelieveful 363 on at

Like (0)

Report

Thanks Andrew,

Is that right you mean that servernext can stems from in memory / temp table if there is no detail information about it?

and my further question then in your case is there any option to disable table cache?

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

Like (0)

Report

Hi Daniel,

Table caching is a property on the design of the table. In one scenario, the server next was hit three times in a row. Due to the number of records, the caching took long, but the record which should be found was deleted as the table too large. The two other calls were looking for the same record, but again the caching caused the waiting time. After changing the table property, we had the performance problem solved.

Was this reply helpful? Yes No