Same Query, Two Plans, One Stinks

@SQLTrooper 2014-08-28 13:08:42

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.

@SQLTrooper 2014-08-28 13:09:44
James 2014-08-28 13:17:20
hate to state the obvious but is there something drastic changing on the statistics side?
@SQLTrooper 2014-08-28 13:35:56
That "parameter list" at the end – are those what the plan(s) were built off initially? They vary drastically (blank values for @P6 and @P7 for the worse plan) so perhaps that's the issue? I cannot control the incoming query from AX, so what are my options to pin that query to the good execution plan? I've read plan guides are not the best option, but might be my only option?
@SQLTrooper 2014-08-28 13:27:37
Well, I do nightly stats/index maintenance and have a custom index rebuild job (which would update stats) that runs prior to master planning (a result of heavy fragmentation – AX is great for that) so I would think they wouldn't deviate all that much from night-to-night/hour-to-hour. But, I do not have the data to say yes or no.

I've only been reading/learning the XML version of execution plans for maybe a month so I was wondering if there is something in there that stands out like a sore thumb, or not.

SQLkiwi 2014-08-28 13:47:58
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:

@SQLTrooper 2014-08-28 15:30:58
Thanks, Paul. Looks like TF4136 may be my best option given it's Dynamics AX – ironically, per your post, and found in another below:

I'll do a little more reading up on the topic before I go implementing in production, however.

@SQLTrooper 2014-09-03 18:49:57
Circling back on this:

I enabled TF4136 but apparently the two sets of parameters were still best suited by the two different indexes based on the joins. I adjusted the better of the two indexes to not be led by the PK – that wasn't real brilliant of me since it's already included with non-clustered indexes. Anyway, after adjusting and reordering the non-clustered, covering index a bit both versions of the query use the same, more efficient index. Unfortunately master planning doesn't run until tonight so the results are yet to be seen – I feel pretty good about it though.