Same Query, Two Plans, One Stinks

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.

avatar image By @SQLTrooper 86 asked Aug 28, 2014 at 01:08 PM
more ▼
(comments are locked)
avatar image @SQLTrooper Aug 28, 2014 at 01:09 PM

This is the "good plan".

avatar image James Aug 28, 2014 at 01:17 PM

hate to state the obvious but is there something drastic changing on the statistics side?

avatar image @SQLTrooper Aug 28, 2014 at 01:35 PM

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?

avatar image @SQLTrooper Aug 28, 2014 at 01:27 PM

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.

10|10000 characters needed characters left

1 answer: sort voted first

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:

http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

avatar image By SQLkiwi ♦ 6.6k answered Aug 28, 2014 at 01:47 PM
more ▼
(comments are locked)
avatar image @SQLTrooper Aug 28, 2014 at 03:30 PM

Thanks, Paul. Looks like TF4136 may be my best option given it's Dynamics AX - ironically, per your post, and found in another below:

http://blogs.msdn.com/b/axinthefield/archive/2010/11/04/sql-server-trace-flags-for-dynamics-ax.aspx

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

avatar image @SQLTrooper Sep 03, 2014 at 06:49 PM

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.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x117
x109
x89

asked: Aug 28, 2014 at 01:08 PM

Seen: 201 times

Last Updated: Sep 03, 2014 at 06:49 PM