Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Performance Issue
Microsoft Dynamics AX (Archived)

Performance Issue

(0) ShareShare
ReportReport
Posted on by Lifeisbelieveful 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.

*This post is locked for comments

  • Verified answer
    guk1964 Profile Picture
    guk1964 10,877 on at
    RE: Performance Issue

    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)

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,711 Super User 2024 Season 2 on at
    RE: Performance Issue

    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.

  • Lifeisbelieveful Profile Picture
    Lifeisbelieveful 345 on at
    RE: Performance Issue

    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?

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,711 Super User 2024 Season 2 on at
    RE: Performance Issue

    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.

  • Lifeisbelieveful Profile Picture
    Lifeisbelieveful 345 on at
    RE: Performance Issue

    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?

  • guk1964 Profile Picture
    guk1964 10,877 on at
    RE: Performance Issue

    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

     

  • Lifeisbelieveful Profile Picture
    Lifeisbelieveful 345 on at
    RE: Performance Issue

    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.

    severnext.png

  • Suggested answer
    guk1964 Profile Picture
    guk1964 10,877 on at
    RE: Performance Issue

    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.  

  • Lifeisbelieveful Profile Picture
    Lifeisbelieveful 345 on at
    RE: Performance Issue

    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.

  • Malcolm Burtt Profile Picture
    Malcolm Burtt 1,475 on at
    RE: Performance Issue

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,711 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,458 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans