Queru Runs Forever without completing

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

avatar image By Deamon 0 asked Apr 07 at 06:50 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

deamon1.png (810.6 kB)
deamon2.png (320.9 kB)
avatar image By Aaron Bertrand ♦ 1.7k answered Apr 11 at 08:26 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x600
x415
x38

asked: Apr 07 at 06:50 PM

Seen: 466 times

Last Updated: Apr 11 at 08:26 PM