Help with Plan
First off, is there any way you can get an actual plan to us, as opposed to just the estimated plan? There are a lot of really important metrics that we can't see when looking only at the estimated plan. For example, I always check the Expressions tab for CONVERT_IMPLICIT. But with an estimated plan, we can only see those kind of important indicators as warnings (the little triangle with an exclamation point). TL;DR – I think that CONVERT_IMPLICIT is really screwing up your index effectiveness.
With that, here are a couple of quick tips:
- The index statistics appear to be way off. When is the last time statistics were updated? If it's not recent or no one knows, then update them soon.
- You have a very large Index Scan (Object13.Index8) pulling back 348Mb of data. Can you tighten that up in any way? Possibly avoid a CONVERT_IMPLICITY on the OUTER JOIN evaluation by explicitly CASTing the search arguments? Similarly, the Index Seek on Object11.Index7 pulls back more than 1Gb of data. Is there a way you could eliminate that data earlier in the execution plan operation?
- With Column12 = Object1.Column8+?+Object1.Column9+?+Object1.Column10 in the SELECT item list, be sure that they are all compatible data typces and, if not, CAST them to be consistent when doing the concatenation. Otherwise, there'll be CONVERT_IMPLICIT operations.
Hope that helps,
in addition to what Kevin said I would add a suspicious LEFT JOIN between Object16 and Object11.
Would it be possible to separate it from the main query by putting to a temp table?