The query itself is ugly and there is stuff that could be changes but it is adequate. What i am curious about is the repetitive nature of the residual predicate on Node 70.
By Dan Holmes 725 asked Oct 10, 2013 at 05:02 PM
It's hard to be certain without being able to run the query myself and dig into the optimization details, but there are a number of relevant factors:
My best guess is that the above factors combine to produce the duplicated predicates. If you are curious about the exact cause, you could try compiling the query with
The query is certainly a candidate for improvement, possibly using temporary tables to break the overall task into more manageable chunks (both for the optimizer, and for humans). This would likely also have the effect of removing the redundant predicates.
You could also move to the latest Service Pack (SP2 for R2) just in case that makes a difference. Advisable anyway.
By SQLkiwi ♦ 6.6k answered Oct 12, 2013 at 03:50 PM
This is interesting (and also occurs in Node 87), but I don't know if I can explain it. This isn't something we're inventing or messing up, though; this is coming straight from the plan XML. If you load it into your favorite text editor, take a look at lines 2889 and 3924; they both look something like this:
The predicate is repeated 15 or 16 times, certainly more times than it is referenced in the query, but not much more. :-)
Sorry I don't have a better answer for you, but this seems to be a product of SQL Server - I suspect you'll see the same repetition if you pulled the plan directly from cache or using SSMS. I realize that the tooltip in SSMS does not display residual predicate information, so it might not be as obvious, but if you highlight either of these nodes in the graphical plan in SSMS, then click F4 (properties), you will see this there as well by clicking on the little [...] button at the far right of the Predicate property (or just by mousing over it). You can also see that there seems to be way more information than necessary under the Seek Predicates set of properties:
By Aaron Bertrand ♦ 1.7k answered Oct 11, 2013 at 09:04 PM