Creating and managing custom indexes in Dynamics AX
Hello,
An index is a database structure that is used to speed the retrieval of information from a table, or in some cases to ensure the uniqueness of records in a table. In Dynamics AX, each table comes out-of-the-box with a variety of indexes designed to provide the best results in most cases. As every Dynamics AX implementation is different, and every workload has its own specificity, there will be situations where you detect that a new index is necessary to improve the performance of a specific process.
In this article I will give you 10 recommendations to create and manage your custom indexes for Dynamics AX.
1- Limit the number of indexes per table, especially if the table is transactional or a table that is frequently updated. Adding an index can be beneficial for retrieving information from a table but SQL Server will have to update the new index every time a modification is done to the data in the table. You may speed up SELECT operations, but slow down INSERT, UPDATE and DELETE operations. Also, a new non-clustered index means a new object in the database. This object will need disk space. Having many indexes on a large table will have a big impact on the overall database size.
2- Unit testing is not enough, do some integration testing and be prepared to rollback your change. The database indexes structure is a delicate and complex equilibrium and adding an index can have unexpected side effects. Make one change at a time and monitor the database to make sure your new index is not affecting other processes in a negative way.
3- Always create your custom indexes from the AOT, the columns PARTITION and DATAAREAID will be added when synchronizing if available. If you create your index directly from the database your changes may be lost at the next AOT synchronization.
4- Document why the index was created.
5- Check if the index is still required after an upgrade.
6- Include columns to improve the efficiency of your new index. You can do this from the AOT since version 2012. Including non-key columns to your index will allow SQL Server query optimizer to obtain all the columns needed, without having to access the clustered index or the table. These milliseconds gain can make a very big difference if the query is run thousands of times inside a loop for example. Limit yourself to 4 or less included columns. Make sure they are static columns and not frequently updated columns.
7- Remove custom indexes that are not used. Periodically review the indexes that are not used in your database. You can use Performance Analyzer for Microsoft Dynamics, or directly querying SQL Server DMVs. Remove only unused custom indexes, keep the unused standard ones. Be very careful when removing unused indexes as some of them are used infrequently, for example during end of month or end of year procedures. Make sure to have enough historical information before taking a decision.
8- Remove duplicate and included indexes. Indexes that are a left key subset of another index in the same table are not necessary unless there were created to ensure uniqueness.
9- Never include RECVERSION in an index, even as an included column, as this column is frequently updated.
10- Make sure your columns are in the correct order to avoid index scans.
Performance Analyzer for Microsoft Dynamics is the tool you need to manage your custom indexes in an efficient way.
I hope this is helpful!
Bertrand
