Lets talk about Indexing

A few months ago I was having a conversation about this subject with my new CIO. As he was new to the role we were going through some of the ongoing projects I had on my plate at the time. Scattered within the mix was an ongoing task to move all the indexes from being only within the database up into the AX2012 AOT. Put another way, transition from data to code. There are a number of reasons to do that which I'll explore momentarily but let's cover off the rest of that conversation. Having recently been through a D365 implementation and therefore having some knowledge of the product set and the associated challenges the statement was made 'Why put effort into that, AX Indexes don't work very well anyway'. To which my response was, delivered with my usual tact and humility, 'Not if you do it properly and know what you're doing'. It's fairly safe to say that particular response wasn't overly well received although the sentiment remains true. Not only are there very compelling reasons to ensure your indexing is within the AOT. Once created they do exactly the same job as they would when created directly within the database, given that once created they are now part of the database itself.

This begs the question as to why we continue to create (and maintain) indexes directly against the database. All of the Microsoft documentation strongly recommends, if not insists, that indexing is created within the AOT. There is actually a really simple answer to this. The database is looked after by a Database Administrator and it's quite rare to find a DBA who also understands how to use the AOT. On the flip side an AX Developer is not generally a DBA and as such when they are required to create an index, said indexing can often be quite simplistic in its structure. This combination tends to result in a path of least resistance being undertaken. In that it will generally be a DBA who discovers the need for additional indexing. usually through a combination of blocking, wait time and locking reports. When the DBA discovers an issue they will either create or remove the indexing directly within the database as that is their area of expertise. Also the change can be implemented outside of the release cycle allowing for a more immediate resolution to the issue. Why not have both? When creating the immediate fix how about creating the AOT version at the same time in preparation for the next release.

Over the years I've been given a number of reasons why indexes cannot be created in the AOT, let's take this opportunity to quell a couple of those myths.

The AOT does not deal with Filegroups. That is absolutely true, to some degree, as the database synchronization process is not filegroup aware an as such all indexes are created in the primary group. Let's think this through for a moment, if the sync process is not aware of the group then it doesn't know what group the index is in and solely looks for the index based on its name to determine the need for removal or recreation within the synchronization process itself. That means if at the point of first synchronization of your index you then move it to the desired filegroup, future synchronizations will be aware of the index and you still get the performance advantages of data segregation.

All indexes in AX start with PARTITION & DATAAREAID potentially making the query engine less efficient in choosing the appropriate index. This is also true as it's the default structure of an index created from the AOT. It's also not true in that these two columns can be positioned anywhere within the index. In whatever column order you prefer. The only thing you cannot do with them is have them as an INCLUDE field, also you have to consider that they both have to exist somewhere within the index. If they do not appear either or both of them will be automatically included at the start of the index at point of creation. Unless the table is global and then it's only about PARTITION. This is probably the point where you look to combine code review with index creation. Looking to make it really efficient, how about changing the field order in the query to match the field order in the index. We can cover off the whole index column order verses data granularity question another time, even though the answer to it usually ends up being 'that depends'.

Having covered a couple of the general reasons which are used for not creating indexes in the AOT. Shall we explore a couple of the compelling reasons as to why indexes should be created in the AOT? Will not be including 'Because Microsoft said we should' in this list as to be completely honest how many of us always do what Microsoft suggest we do anyway. The exception being the path to Certification where you have no choice but to follow the rules.

Let's say you're upgrading from AX2012 to D365 Finance and Operations. You've done a lot of work on the indexes over the years and have confirmed that the future state data structure is fundamentally the same, there are a few differences but we can deal with those in the upgrade project. Now for the bad news, you don't have direct database access in D365. Unless your indexes exist in the AOT they won't exist at all and cannot be created directly against the database. Transitioning to AOT based indexing prior to upgrade will allow for future system performance to be assessed against the current base point. By continuing down the path of creating indexes directly within the database you are simply generating Technical Debt which will eventually have to be addressed.

Now for one of my favourites, Deployments. In particular the potential size of the outage window. There are a number of tasks performed as part of the deployment, be it clearing cached files or running a CIL. The specific task which is impacted here is the Database Synchronization which is a standard and required step in any deployment. So physically, what does this synchronization do? In relation to indexes it looks at the list of indexes in the AOT and if it exists in the database with the same fields and in the same order etc. it does nothing. If the index only exists physically in the database and not in the AOT it drops it. What's the impact of this? To use an example, in a recent customer the rebuild of the indexes post deployment added a couple of hours to an already tight deployment outage window. The time this index regeneration takes is of course dependent upon database size and the number of indexes not in the AOT. There is, of course, a secondary impact of this index recreation process. By dropping and recreating your index did you not just reset the indexes statistics? The query engine starts the learning curve all over again.

There you have it. I hope this article has helped not only to explain that some things deemed not possible are achievable. While also giving some reasons to make the leap to creating indexes in the AOT and why it it is required. Although given all this, the final reason to undertake the index transition is 'Because Microsoft said we should' and now the future state solution is structured in such a way that, in reality, we are left with little choice but to finally follow the rules.