Make AX / 365 F&O Performance Better

Recently I read a post complaining about 365 F&O performance. There are some performance issues I experienced in AX / F&O personally:

-AX / F&O own structure

-Customizations

-Hardware issues

Update Methods

In AX / F&O there is a strong integrity. Lots of methods check updates, controls relations, validations etc... And set updates are not really set updates, this's why a big volume update -even in case using update_recordset- so slowly. AX / F&O runs some methods during and before insert/update/delete (insert, update, delete, methods and logging) . You can skip these methods in "some situations" if your update won't need to run these methods (Sample a custom field you know doesn't need any calculation or update else).

???MyTable myTable;

???myTable.skipDeleteActions(true);

???myTable.skipDatabaseLog(true);

???myTable.skipDataMethods(true);

???myTable.skipEvents(true);

SQL Server Tweaks

Please note that; I'm not a SQL Server expert.

Make "Optimize for Adhoc Workloads = True" would be good for your SQL Server performance.

Arrange your SQL Server memory limit by PLE (Page Life Expectancy = The number of seconds the average page of data has been in?the buffer pool) value definitely works (At least worked for me). Run this script in your SQL Server separately. If server ram as GB /4 * 300 is lower than this script's output than you need more ram:

SELECT [object_name],

[counter_name],

[cntr_value] FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Manager%'

AND [counter_name] = 'Page life expectancy'

Customizations

Usual suspects are customizations. I'll give a list, I know a big part of these cliche but still we developers do same mistakes:

-Do not use the display method, instead try to use the inner join or computed columns in view ( computed columns make your query slower too but not as slow as display methods). If inner join would not be possible try to use display method cache.

-PostLoad method great for populating some data in some situations but be careful and try to don't use, it will make your queries slower.

-Do not use query in query, try to join. Try to use exists join -if possible-.

Don't:

While select myTable

{

select firstonly myNames where myNames == myTable.Name;

....

...

}

Do:

While select myTable

exists join myNames where myNames == myTable.Name

{

...

...

}

-Try to use set insert/update/delete, insert recordlist.

-Do not add an index -unless it's definitely required- . A new indeks would be useful for your report but will cost as slower insert/update/delete.

-If you want to use a combined index partially, you have to respect field order. If you don't start with the first field in the index, SQL Server won't use that index.

If you run as;

select select custTrans where custTrans.TransDate == myTransDate && custTrans.Voucher == myVoucher

this won't work as index optimized. You have to use as:

select select custTrans where custTrans.TransDate == myTransDate && custTrans.Voucher == myVoucher && custTrans.AccountNum == myAccountNum

Years ago, in AX 2009 there was a code about select for returned items, it was working with that return code and there wasn't any index about that code. We decided to not create a new index just for a select using seldom, just added itemid in that select code and it started to work as partial indexed and the problem was solved. If we would add a new index that would be faster for that select but would be slower for updates.

Also don't forget to add Data Area Id and Partition fields for your external reports (X++ adds these fields for your queries automatically) .

-Do not add a new field or add a new index to InventTrans -unless that's really really necessary-. InventTrans table maybe most used table (sales/purchase orders, transfers, invoice, packing slip etc...) with SourceDocument tables in ERP, in a lot of situations one order line will split one more than InventTrans record, and ERP runs an amazing volume of update in InventTrans.

-There are lots of clean up jobs in AX menus, run these jobs (These jobs will run slowly, you can run your own from SQL Server for better performance).

-If you run previous versions than F&O (AX 2012 and lower versions), queries in forms will run in the client, this makes more network traffic and slower AX.

-Again if you run previous versions than F&O, don't use AX client in your local computer, use RDP instead for better performance.

-Do not fetch all fields, fetch just what you need:

don't select custTable;

do select AccountNum,VATNum from custTable;

-Use firstline keyword with select-sql if you're sure just one line will be fetched (I don't know why, but they say this's faster).

-Try to use a data warehouse for reporting. This'll make your AX faster, you'll have better PLE values.

Google for some useful pages - like I did :) - :

https://www.dhirubhai.net/pulse/top-10-actions-troubleshoot-performance-dynamics-ax-master-caillet/

https://github.com/pfedynamics/dynamicsperf

https://www.dhirubhai.net/pulse/dynamics-ax-data-clean-up-part-1-introduction-best-anna/

https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/microsoft-dynamics-ax-intelligent-data-management-framework-idmf

https://www.alithya.com/en/insights/blog-posts/data-cleanup-utilities-in-microsoft-dynamics-ax

https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/clean-up-closed-and-unused-on-hand-inventory-transactions