Queru Runs Forever without completing

Deamon 2017-04-07 18:50:00

I have been running this query for about an hour and it did not finish. I have looked at the query plan and it did not show anything wrong with it.

Please I really do need help optimizing the queries

Aaron Bertrand 2017-04-11 20:26:24
Well, I'm not so sure… if you hover over the tooltip for the top-left SELECT operator, you can spot multiple problems:

alt text

Namely:

  1. an optimal plan could not be found;
    SQL Server timed out trying to find
    one (largely due to the complexity
    of the query)
  2. an overall cost of 111 – this seems pretty high
  3. a whole bunch of implicit conversions, which can prevent seeks and cause issues with estimates
  4. a missing index opportunity

Nothing in the graphical plan really stands out because the work is spread out across so many operators, even we have a hard time making the most painful part obvious:

alt text

If you go to the Top Operations tab, and sort by estimated I/O cost, you'll see that the largest cost is the scan on Object13.Index3. Because you've anonymized the plan, it's impossible to tell what the predicate is (I couldn't find Object13 in the query text), but perhaps there is a way to change the predicate (or the index) such that a scan isn't required. The next most expensive is Object1.Index11 (also not found in the statement text).

But let's be realistic. Even if you do improve these few expensive operators, this is still a whopper of a query for the optimizer to try and wrangle. Instead of having all these CTEs, you might want to consider materializing some of those results into intermediate temp tables, which (a) give the optimizer more flexibility, (b) provide for the ability to create additional indexes and localized statistics, and (c) prevent CTEs that are referenced more than once from being materialized more than once.