Queru Runs Forever without completing
Please I really do need help optimizing the queries
- an optimal plan could not be found;
SQL Server timed out trying to find
one (largely due to the complexity
of the query)
- an overall cost of 111 – this seems pretty high
- a whole bunch of implicit conversions, which can prevent seeks and cause issues with estimates
- 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:
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.