You’re offline. This is a read only version of the page.
Announcements
345
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.
Generally cache improves performance - but its not a good for tables that are frequently updated. An EntireTable cache is flushed whenever an insert, update, or delete is made to the table. At the same time, the AOS notifies other AOSs that their caches of the same table must be flushed. After the cache is flushed, a subsequent select on the table causes the entire table to be cached again. Therefore, avoid caching any table that's frequently updated. Regardless of when updates are made, EntireTable caches are flushed every 24 hours by the AOS.
There is a disableCache method on the xRecord object, which takes a boolean flag as a parameter.
Example:
hcmBenefit.disableCache(true); //this will disable the cache
hcmBenefit.disableCache(false); //this will enable the cache
For documentation about this method: docs.microsoft.com/.../gg929136(v=ax.60)
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.
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?
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.
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?
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
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.
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.
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.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,186
Super User 2024 Season 2
#2
Martin Dráb
227,996
Super User 2024 Season 2
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (