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.
There are 27 indexes in AX 2012 R2. So what’s next…
These indexes are added, when specific features in AX are created. But do you use these features? Even when the configuration key is turned off the index is still there. Trust me, to maintain 27 indexes on a table is a huge overload. So how can we get right of these indexes?
The trick is to change the property Enabled to False. Next time you synchronize the database, they will disappear. But now you have to determine the obsolete indexes. I can give you some guidance:
- Unique indexes are never obsolete
- Indexes with 0 seeks are really not used. But check this over a longer period.
- Indexes with more updates then seeks are expensive.
- How unique is this index?
Find the answers to these questions and you know if it is obsolete. You have to investigate the sys.dm_db_index_usage_stats in SQL. In case this is too complex for you, an alternative could be the Kaya Performance Dashboard. This will collect all your data so you only have to set the property Enabled to False on the index in the AOT.
I can guarantee that the performance will double.
The new index features in AX 2012 are nice, but in become tricky in combination with integrations like DMF or WCF.
The DMF wizard results duplicates the table to the DMF staging table with the same indexes, only 2 columns are added. When filling the staging tables , it work fine, unless you start pushing the data to the target, you get strange errors like:
Cannot edit a record in DMF**** (DMF****). Update on a valid time state table is not allowed without specifying a ValidTimeStateUpdateMode
You can solve this one by changing the index on the DMF**** table. (DMF copies the table including the indexes.) Set the ValidTimeStateFieldType property to non (only possible on an empty table). Next add the from and to field manually to the index. So far so good, now we must make one more adaption so we can also update the target
public Common insertUpdate(Common _target, boolean _callInsertLogic = true, boolean _callValidateLogic= true)
if(_target.TableId == tableNum(****))
target.ValidFrom = today();
target.ValidTo = dateMax();
ret = super(_target, _callInsertLogic, _callValidateLogic);
Even when you only create records in AIF , the framework also does updates. The consuming party gets errors like:
- “The ‘Expiration’ value needs to be greater than or equal to the ‘Effective’ value.”
- “Update on a valid time state table is not allowed without specifying a ValidTimeStateUpdateMode”
- “Entity update mode cannot be specified”
You could solve this by setting the updateMode in the prepareForSave.
public boolean prepareForSaveExtended(
// Process MarkupAutoTable records
axbc_MarkupAutoTable = _axBcStack.top();