goshoom: Index fragmentation on xRef tables

[Blog bot]

Источник: http://dev.goshoom.net/en/2014/06/in...n-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.



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



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.



Источник: http://dev.goshoom.net/en/2014/06/in...n-xref-tables/