why not seek predicate

NEERAJ PRASAD SHARMA 2014-09-16 11:39:35

hi,

Here in the query optimizer know what exact range goona come but insted of chosing seek predicate it is choosing predicate here,

PS: just testing , i know chang the syntax will create a better and optimal plan

SQLkiwi 2014-09-16 14:01:24
The uploaded plan performs a separate seek into the target table for each value specified in the IN list, then performs a distinct sort to honour the duplicate-eliminating semantics of IN (side note: value 88 is listed twice in the sample).

If I understand correctly, your question is why, when SQL Server performs a seek on each specific value, it also applies a residual predicate testing ID >= 1 AND ID <= 108: Seek properties

This results from the application of "implied predicate" logic. SQL Server can see that the smallest value in the list is 1 and the largest value is 108. It creates the implied predicate ID >= 1 AND ID <= 108 from this observation. The "implied predicate" mechanism is quite general. It can enable significant optimizations in other scenarios. Here, the residual predicate adds no value at all. It's presence in the final plan is a small redundancy that will have little effect on observed performance. Undocumented trace flag 2324 can be used to disable implied predicates, in case you are interested.

Update:

The execution plan operates as follows:

  1. Execution starts with the sort, which asks for a row from the nested loops join
  2. The join asks for a row from the Constant Scan (value returned = 1)
  3. The join passes control to the Seek, providing the correlation value (1)
  4. The seek finds the first row matching ID = 1
  5. The seek applies the residual predicate ID >= 1 AND ID <= 108 (redundant)
  6. The row passes back through the join to the sort
  7. The sort asks for another row from the join
  8. The next row matching ID = 1 is returned from the seek
  9. When no more rows come from the seek…
  10. The join asks for a row from the Constant Scan (value returned = 2)
  11. The join passes control to the Seek, providing the correlation value (2)
  12. The seek finds the first row matching ID = 2
  13. The seek applies the residual predicate ID >= 1 AND ID <= 108 (redundant)
  14. This process continues until the sort has all rows from its subtree
  15. The distinct sort is performed and then starts returning rows to the client

I hope that clarifies things.

NEERAJ PRASAD SHARMA 2014-09-17 07:28:11
HI PAUL,

does execution plan says it seeking predicated row by row using index id
with the predicate every time.

for example for id 1 for seek with predicate for range rows
then for id 2 seek then again predicate for range row
or
pull the predicate once then seek data from it row by row using loop join.

and you said this technique "implied predicate" significant optimizations in other scenarios. so would you like to share some.

SQLkiwi 2014-09-17 10:26:38
Updated the answer to hopefully clarify the details. I don't have a simple example of useful implied predicates to hand. I will write an article about it most likely.