why not seek predicate

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

avatar image By NEERAJ PRASAD SHARMA 50 asked Sep 16, 2014 at 11:39 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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

  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

  14. ...

  15. This process continues until the sort has all rows from its subtree

  16. The distinct sort is performed and then starts returning rows to the client

I hope that clarifies things.

sp.png (24.1 kB)
avatar image By SQLkiwi ♦ 6.6k answered Sep 16, 2014 at 02:01 PM
more ▼
(comments are locked)
avatar image NEERAJ PRASAD SHARMA Sep 17, 2014 at 07:28 AM

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.

avatar image SQLkiwi ♦ Sep 17, 2014 at 10:26 AM

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.

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:

x588
x405
x1

asked: Sep 16, 2014 at 11:39 AM

Seen: 2719 times

Last Updated: Sep 17, 2014 at 12:10 PM