Where to start troubleshooting?
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.
Before it worked around 3 hours.
Now – more than 10.
No big changes were made to the data or to this query.
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.
If you fix your key lookup you should see some benefit.