Welcome to Blog

SQL Trace Flag for Dynamics Ax: Do we need it?

What is a SQL Trace Flag? A SQL Trace Flag is an indicator that changes the way the SQL engine reacts. Why would we want to that? Well because the DBA always knows best, uh well actually because the standard behavior of the SQL engine will not always give the best possible performance for a Dynamics […]

SQL Window Functions in Ax? Yes we can

Are SQL Window Functions new? Since 2003 in the SQL ANSI/ISO standard and extended in 2008. Microsoft implemented the first window functions in their SQL Server 2005 version (known as OVER clause) and extended them in the following versions. So to be short no they are not new for the ANSI/ISO standard and for Microsoft SQL Server window functions […]

Compression: Less storage AND improved performance

Let us have a look at SQL Compression, with this I mean Data compression not the backup compression which is also available in SQL Server nowadays. This feature can be implemented in SQL Server without affecting the programs that use the database, because it is only used inside of SQL Server and the way it […]

Index fragmentation effects on performance

Before jumping into index fragmentation I’ll first explain what indexes are and how they are used by the SQL Server Engine. I tried to compare it to a real world way of searching data, to paint a clear picture in your head. I’m not going into the differences between clustered and non-clustered indexes as that […]

Missing index will cause slow performance: SQL can help you

Introduction As a new member of the Kaya family I feel the urge to introduce myself. Hi I’m Boye Walters, a 34 year old developer with over 12 year experience in Microsoft Dynamics Ax and Microsoft SQL Server. Having worked with all versions of Ax ever released in the Netherlands I can truthfully say that […]

Obsolete index

We all know indexes on AX tables will improve performance when querying these tables. There are all kinds of fancy features such as parameter sniffing, trace flags, etc. But if we look for example at the ledger Journal Trans table you will start thinking that maybe core dynamics AX has added too many indexes.

Unretrieved values on the company info Table in AX 2012R2

Table inheritance in AX 2012 R2 is different on SQL level in case you compare it to AX2012. For people that don’t know what table inheritance is look at the next picture.