Where to start troubleshooting?

GK 2016-12-16 12:59:54

I have a query with this execution plan.
Before it worked around 3 hours.
Now – more than 10.
No big changes were made to the data or to this query.

Matak 2016-12-20 03:01:39
Im guessing this is AX or something similar and the query is generated from that.
If you fix your key lookup you should see some benefit.
Aaron Bertrand 2017-01-08 16:45:23
Looks to me like adding ECC_DEFECTREASONID, INVENTSIZEID, and INVENTCOLORID to the index [I_698ECC_BUSUNITLOCIDX] on dbo.[INVENTDIM] would eliminate the costly key lookup. Though I have a couple of disclaimers:

  • If this is AX, I don't know if changing the index (or adding a new one) does anything to your support agreement
    • I don't know what impact making this index wider might have on the write portion of your workload
    • I don't know at what point the clustered index might be the better choice

If you can generate an actual plan from within Plan Explorer, instead of opening a plan generated by Management Studio, the Index Analysis tab will have a lot more insight for you.