I have a Dynamics AX query that executes over 25,000 times during master planning each evening. When it uses one execution plan, those executions amount for maybe an hour. When it uses another, it amounts for over five hours (a half second per execution, which is huge given the execution count).
I'm uploading the actual execution plans and am curious if anything real obvious may indicate why it uses an "efficient" index seek using I_698INVENTDIMIDX in the one case, and the "inefficient" index seek + key lookup using I_LOCATIONIDIDX in the second case.
By @SQLTrooper 86 asked Aug 28, 2014 at 01:08 PM
The decision is more about in which order to perform the joins, rather than simply choosing between a lookup and an index on one particular table. Ultimately it's a question of estimated cost, and the equation is driven by the parameter values present when the query was compiled ("parameter sniffing").
The specific parameter values present when the plan was compiled can be seen in the Parameters Tab of Plan Explorer. Once a plan is compiled (and optimized based on the initial values) it may be reused many times, until something causes a recompilation. The two plans you supplied have different parameter values.
So it seems you have a fairly typical parameter-sensitivity problem, where the cached-and-reused plan depends on the parameter values present at compile time. You can find further discussion and a list of actions you might choose to take in the following SQLperformance article:
By SQLkiwi ♦ 6.6k answered Aug 28, 2014 at 01:47 PM