Keep Away From Index Hint in the Query Tuning

What is the Index Hint keyword in X++? The Index hint statement will give Query Optimizer information about which index to use for fetching and sorting data, whether it is good or not. In this blog we are talking about the risks associated with using the Index hint statement to enhance query performance, to highlight it should never be the FIRST choice.

Importance

How to detect if an index hint is used

This blog post won’t go into detail on how to use an index hint, to learn more you can read Select statement - Finance & Operations | Dynamics 365 | Microsoft Learn. Instead we’ll focus on the following simple example, where the ITEMIDX index is used to sort the records in the query on the InventTrans table. Note, only indexes created in the AOT can be used for index hint.

1
2
3
4
5
6
7
8
9

public static void main(Args _args)
    {
        InventTrans                 inventTrans;
        inventTrans.allowIndexHint(true);
        Select sum(CostAmountPosted), sum(CostAmountAdjustment) 
	from inventTrans
            index hint ItemIdx
            where inventTrans.DatePhysical <  str2Date('2022-12-31', 213);
    }

The SQL statement will look like the below if the index hint is used.

1
2
3
4
5
6

 SELECT Sum(T1.costamountposted),
       Sum(T1.costamountadjustment)
 FROM   inventtrans T1 WITH (INDEX(i_8479itemidx))
 WHERE  ( ( ( partition = 5637144576 )
           AND ( dataareaid = N'usrt' ) )
         AND ( datephysical < @P1 ) ) 

SHORT-TERM workaround instead of LONG-TERM solution

The best practice is to allow Query Optimizer to decide the most efficient way to process queries instead of forcing it to use a certain set of indexes pre-determined in code. As you know, index hint has been deprecated since Dynamics AX 2012, the reason for this is because is this feature began to create complications in the evolving development environment, however it has been re-enabled since Dynamics 365 for Finance and Operations platform update 23. So, index hint is an official keyword in X++, so this begs the question as to why index hint has been introduced? The reason is so that developers can manage exceptions, that is when index hint is the best solution to the performance problem, however it should never be used as the rule. Most of the time Query Optimizer reliably chooses the most effective index, however, sometimes Query Optimizer could select an index that isn’t the most performant. If Query Optimizer isn’t using the expected index and you know your query will be more performant with the index you want to specify, then it can be done using index hint, however as mentioned above, it will raise the risk that future changes could cause the specified index to become non-performant.

In general, the Index hint keyword is almost never the correct long-term solution for fixing query performance, there are pros and cons for all decisions and there is no exact guidance when index hints should be used. If it is necessary to use index hint, it should be seen as a short-term workaround instead of a long-term solution and the developer should make note to remove the index hint once a long-term solution has been implemented.

Limit of scenarios

While we recommend against using index hint, sometimes there are scenarios where it could be considered. Below are few examples of such scenarios.

1. Working in a scratchpad table with highly volatile data that gets deleted after a calculation process is completed where index statistics cannot be updated frequently enough to allow Query Optimizer to select the correct index.
2. Your system is running in 24/7 so it is extremely hard for you to create missing indexes to optimize query performance on the largest tables, e.g. InventTrans, InventDim, GAJE..etc, and you’ve tested and found an index that can improve the performance but Query Optimizer hasn't picked it.
3. Index hints can be good to use for investigating, testing, and debugging. Specifically when you are troubleshooting poor performing queries. In that scenario you can use Index hint to select an alternate index to see how much impact the change has on the process time.

Consideration if using index hint (You need to take the responsibility when using Index hint)

1. Performance testing is extremely important, review and analyze the result to ensure they are positive when using index hint.
2. Maintain any “Index hint” queries regularly; regression testing must be completed for all involved queries in every deployment. Microsoft updates will sometimes reshape indexes e.g. add/remove columns in the system indexes, if your query with index hint uses an OOB index this could lead to decreased performance.

Avoid index hint, use-with-caution if necessary

It is not recommended to use Index hint to resolve a performance issue with a query even if you find that the performance issue is due to an wrong index being used. Instead look for the root cause of a poor performing query “Why is Query Optimizer selecting the wrong index?” And create a suitable index or rewriting the query. These options will likely help you create a better, long-term, flexible solution rather than forcing SQL Server to use the same hard-coded, potentially sub-optimal index forever.

Microsoft has delivered enhanced monitoring features of environments since 2021 release wave 1 plan and deprecated some SQL actions from LCS, thus activities and monitoring will be performed internally, by the platform, through automation. The Dynamics team helped improve many areas of the database engine. One example is Data Administration and Management Service (DAMS) which has been developed to schedule and execute maintenance tasks and jobs like creating or rebuilding indexes to dynamically optimize changes in customer workloads. These tasks can span areas like performance improvements, transaction management, diagnostic data collection and query plan management.

Therefore, we are strongly discouraging developers to apply index, but if you do decide to use an index hint, please ensure you are aware of the above discussion, and ensure you conduct the necessary performance tests. Index hint should be a short-term fix for your investigation, testing, debugging or workaround purpose, please remember that it should not be a long-term solution for fixing query performance in Dynamics 365. As our note in the What's new or changed in Dynamics 365 for Finance and Operations platform update 23 (January 2019) - Finance & Operations | Dynamics 365 | Microsoft Learn, "You should use index hint sparingly and with caution, and only when you can be sure that it improves performance. The index hint keyword and API let you pass the correct hints when they are required. If you're ever in doubt, avoid using index hint".