A Happy Database = Happy Users for Microsoft Dynamics AX 2012

Posted on: May 20, 2016 | By: Jarrod Kraemer | Microsoft Dynamics AX/365

Authored by: Craig Leska- Senior Technical Consultant

AX performs optimally when the database is running smoothly and to keep things humming along some basic maintenance tasks need to be carried out.

Re-indexing

Dynamics AX is not generally affected by heavy fragmentation, with that said, you should still re-index periodically.

The following query will show the fragmentation level of your database.

As you can see the fragmentation levels are quite high on my sample database before compared to after running the command.

 

 

SQL command to rebuild or reorganize your indexes

When fragmentation is greater than 30% you will want to rebuild the indexes, when fragmentation in between 10% and 30% the index should be reorganized. These are the default parameters in the below SQL command.

Rebuild an index: This process drops the existing index and recreates it.

Reorganize an index: This process reorganizes the leaf node of the index.

The re-index process is best done off hours as there is a potential for performance slowdowns and table locking while the process is taking place, unless you are running SQL enterprise, which offers online re-indexing.

The above re-index can be carried out weekly by a scheduled SQL job.

Statistics Update

SQL server uses statistics to determine the best execution plan to return the data to you. There are Table Scans, where each record is looked at and Index Scan/Seek where the query will be optimized and use the index to its advantage, usually a lot faster than a table scan.

SQL will use the best execution plan based on the statistics. However, you may want to consider updating your statistics as needed. Best practice is to leave Auto Update Statistics set to True, the default behavior in SQL.

Some exceptions are if you have a large table the auto update may not have taken place and when you open an AX list page the wrong execution plan may be triggered and you are doing a table scan instead of a seek. For these very large tables Microsoft recommends updating the statistics nightly, the two most common largest tables are LedgerTrans and InventTrans table. The command to update the statistic on these tables are:

UPDATE STATISTICS LedgerTrans WITH FULLSCAN

UPDATE STATISTICS InventTrans WITH FULLSCAN

 

Full scans can take hours, you may be better off with a sample, as in the below example.

 

UPDATE STATISTICS LedgerTrans WITH SAMPLE 50 PERCENT

UPDATE STATISTICS InventTrans WITH SAMPLE 50 PERCENT

 

Of course there are other factors to look at. If you need help with your AX environment or have performance issues you want reviewed, please give Logan Consulting a call.

For additional information please feel free to reach out to us at info@loganconsulting.com or (312) 345-8817. 

All the best! 
Logan Consulting 
www.loganconsulting.com

 

---

---