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.

There are 27 indexes in AX 2012 R2. So what’s next… image001

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.

image003

I can guarantee that the performance will double.

Valid Time State Tables and Date Effective Data in combination with DMF and WCF (AIF)

The new index features in AX 2012 are nice, but in become tricky in combination with integrations like DMF or WCF.

DMF

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)
{
Common ret;
if(_target.TableId == tableNum(****))
{
if(!target.ValidFrom)
{
target.ValidFrom = today();
}
if(!target.ValidTo)
{
target.ValidTo = dateMax();
}
target.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
_target.selectForUpdate(true);
_target.data(target);
}
ret = super(_target, _callInsertLogic, _callValidateLogic);
return ret;
}

WCF (AIF)

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(
AxdStack _axBcStack,
  str _dataSourceName,
AxdRecordProcessingContext _recordProcessingContext,
AxInternalBase _childRecord)
{
AxMarkupAutoTable axbc_MarkupAutoTable;
switch (_dataSourceName)
{
// ———————————————————————-
        // Process MarkupAutoTable records
        // ———————————————————————-
       case #MarkupAutoTable_DataSourceName:
axbc_MarkupAutoTable = _axBcStack.top();
switch(_recordProcessingContext)
{
case AxdRecordProcessingContext::BeforeChildRecordProcessed:
if(axbc_MarkupAutoTable.currentRecord().RecId)
                  {
                     axbc_MarkupAutoTable.currentRecord().validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
}
return true;
}
default:
error(strfmt(“@SYS88979″,classId2Name(classidget(_axBcStack.top()))));
return false;
}
return false;
}