Skip to main content

Notifications

Microsoft Dynamics AX forum
Answered

Performance Issue

Posted on by 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.

  • 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... 290,186 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... 290,186 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.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Dynamics 365 Community Newsletter - August 2024

Catch up on the latest D365 Community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,186 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 227,996 Super User 2024 Season 2

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans