NodeID 70 has a very repetitive residual predicate. Why?

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.

Plan.pesession (32.9 kB)
avatar image By Dan Holmes 725 asked Oct 10, 2013 at 05:02 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

What I am curious about is the repetitive nature of the residual predicate on Node 70.

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:

  1. The optimizer contains logic to create implied predicates in various situations. For example, where a predicate is applied to a key column on one side of a join, the optimizer adds the equivalent predicate to the other side. The rule generally responsible for this activity is ImpliedPredInnerAndAllLeftJn. There are other mechanisms.

  2. The optimizer also contains logic to tidy up redundant predicates.

  3. The query hint FORCE ORDER works by disabling rules that might rearrange the tree. This is a very powerful hint with a wide range of side-effects. Your query does not use this hint explicitly, but it does include a join hint (`INNER HASH JOIN`) which implies FORCE ORDER.

  4. The query contains a view that is expanded before optimization.

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 OPTION (QUERYRULEOFF ImpliedPredInnerAndAllLeftJn). This may remove the redundant predicates, in which case you know a little more about the cause. You would not want to use that in production of course.

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.

Removing the HASH join hint would also probably remove the duplication (if my intuition is correct) but it would generate a different (and presumably less optimal) query plan.

You could also move to the latest Service Pack (SP2 for R2) just in case that makes a difference. Advisable anyway.

avatar image By SQLkiwi ♦ 6.6k answered Oct 12, 2013 at 03:50 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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:

 <ScalarOperator ScalarString="[RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime] 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime] 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime]
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime] 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime] 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime]
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime]
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime]
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime]
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime] 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime] 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime]
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime] 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime]
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=[@StartDateTime] 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=[@EndDateTime] AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]>=dateadd(day,(-2),[@StartDateTime]) 
 AND [RM_LA_CATS].[dbo].[tblCalendar].[CalendarDate] as [cal].[CalendarDate]<=dateadd(day,(2),[@EndDateTime])">

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:

alt text

holmes.png (33.7 kB)
avatar image By Aaron Bertrand ♦ 1.7k answered Oct 11, 2013 at 09:04 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.