web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AX Kernels translate X...
Microsoft Dynamics AX (Archived)

AX Kernels translate X++ into which SQL ANSI version?

(0) ShareShare
ReportReport
Posted on by DJF94 25

Hi

I am trying to find info as to which SQL ANSI version the different AX kernels translate X++ statements to.

I am interested to find out AX kernel versions from as far back as AX 4.0 and up to Dyn365.

Is there anywhere to find for each kernel version/hotfix what SQL ANSI version it translates X++ into?


Main reason is for some older AX 2009 customers with performance issues we have been advised AX is producing ANSI-89 and can it be changed to ANSI-92 by the SQL DBA. So I am hoping there might be a later kernel update that will allow for this and so the customer can consider.

Thanks

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    I am afraid you will not be able to determine this, the AX kernel is closed and there are no documentation about your request regarding ANSI. T-SQL statement creation happens transparently in the background. There is no option to interfere with what and how the kernel does in any versions.

    Also I have my doubts that if you have performance issues, this would help with addressing it. You must look somewhere else, like what SQL Trace Flags are enabled (there is a recommendation list for AX), how are your resources (CPU, internal/external memory pressure, I/O), do you have query plans timing out, etc.

  • DJF94 Profile Picture
    DJF94 25 on at

    Many thanks

    The SQL expert advised ANSI-92 SQL statements will perform better than ANSI-82 so was looking for low hanging fruit in terms of a kernel upgrade.

    We are looking at all the other areas SQL performance factors but were hoping there might be something we can do within query optimisation that would have an impact across all exiting queries without any re-writing.

    I am interested to hear of anyone else who has done some optimisation of queries from within AX or SQL for AX, especially around the InventDim table (i.e. customers with Batch or Serial number tracking).

    Many thanks and look forward to sharing with others on this topic as in general I feel AX does not do a great job of being optimised with SQL but I am no expert.

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    I've released an article some time ago on keeping AX database and SQL performance healthy, where the first link goes out to the Microsoft Premiere Field Engineer blog that summarizes important areas to check. Have a read:

    [View:https://www.daxrunbase.com/2016/05/11/ax-database-tuning-and-maintenance/:750:50]

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    If you say you're "looking for low hanging fruit in terms of a kernel upgrade", I assume you mean the latest kernel for the given release of AX, but then I don't understand your doubts.

    You should use the latest kernel in either case, to get all latest updates, including performance-related fixes. But a mere hotfix isn't going to change how queries are constructed.

    Which feature SQL-92, that you hope will increase performance of your query, are you missing in SQL generated by AX?

  • DJF94 Profile Picture
    DJF94 25 on at

    Thanks Martin,

    This is AX 2009. Customer needs to be convinced of in the value of incurring costs to upgrade the kernel as they have been running current version for MANY years. If a kernel provides ANSI-92 this would be something we could take to them. If there is somewhere to see performance only related changes in each kernel I would love to know where to see that information!

    The type of queries which would hopefully benefit from with ANSI-92 I will need to get the SQL guy to give me. My understanding was for reducing doing cross-joins to doing inner-joins but that might be totally wrong as I am no expert. Will aim to get clear and post back.

    I also understand SQL itself will potentially optimise the query regardless of what formatted SQL statement syntax it gets but the SQL guy assures me there is benefit to passing ANSI-92 rather than ANSI-89 for certain queries that he profiled AX kernel was sending.

    Just wondering does the AX compiler best practices check for how best to structure a X++ select statement in regards to how that X++ will get translated into a SQL statement by the kernel? I understand it will flag SELECT * code but does it go to any deeper level from a SQL select point of view?

    Likewise is there any info/guide on X++ coding of select statements that explains how AX kernel translates these to SQL statements so we can potentially rewrite X++ to get it to create the SQL statement we want?

    Finally the forceliterals and placeholders, is there any examples where these are good to use? Vilmos mentioned in the hard coded DataAreaId done by the AX kernel but is there any other scenarios these might make sense (e.g. if a customisation was done to add a Sales Order Type to the SO header and a lot of functions or reports within AX where always run filtered by one or two of these types?)

    Thank you both once again for your insightful replies

  • Martin Dráb Profile Picture
    Martin Dráb 237,795 Most Valuable Professional on at

    Installing the latest kernel is nothing expensive.

    Aren't you talking about an upgrade to another version of AX? If so, it's a very different thing than updating the kernel and the cost of upgrade is related to upgrading application code and data. But then I wouldn't call it the low hanging fruit.

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    It is important to be on the latest kernel hotfix, but it rarely brings performance benefits - those should be addressed elsewhere.

    It addresses issues like potential memory leaks that could lead to a crash, functionality that is misbehaving and causing problems, improved communication protocol between client and server, and a lot more for which you should really consider applying the latest one for your major version.

    As for select statements there are a lot of best practices, like use field lists in select statement, use exists join if you do not need records for the joined child table instead of an inner join, minimize the use of aggregate functions and grouping since that is usually expensive, put more unique fields up front in the where clause, or fields which filter out a larger portion of the data first. These all help with getting optimized and faster query plans.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans