Index fragmentation on xRef tables

Building cross-references for code in Dynamics AX creates millions of records in xRef tables. It has also very negative impact on fragmentation of database indexes.

This is an example of indexes on xRefReferences table from a customer’s environment after rebuilding all cross references.

Before

And here the same set of indexes a few minutes later after a rebuilding indexes:

After

If an index is heavily fragmented, it’s much slower to scan it and you’ll notice performance degradation of all queries using the index. It’s not unusual that opening Add-ins in the context menu in AOT takes several seconds just because of a simple check to xRef tables (to see whether cross-references exist for the given object). And because the index can’t be used effectively, of course.

It’s should be obvious that having right indexes isn’t sufficient – they also have to be in a good shape.

It’s wise to have a scheduled job to maintain fragmentation and statistics of all indexes, nevertheless you may also want to rebuild indexes for xRef tables immediately after updating cross references.

Leave a Reply

Your email address will not be published.