Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each:

SELECT A.COL1, B.COL2, C.COL3
FROM A 
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID 
WHERE COL20 < 10000  ---- COL20 IS NOT THE PARTITION KEY COLUMN

In the actual query execution plan, for one of the tables, there is a non-clustered index scan with a key lookup.

When I look at the properties for Key lookup in the actual execution plan, it looks like it's getting partition pruned.

I'm confused as to why that would happen, is this like a negative impact on the system. I understand that key lookups themselves are bad, but why does it show that only 7 partitions have been accessed? The properties say:

Key Lookup (Clustered)
----------------------------------
Actual number of rows:      215805
Actual Partition Count:     7
Actual Partitions Accessed: 3..9
share|improve this question
add comment

1 Answer

Only rows in 7 partitions qualify the predicate(s) so there is no need to ever lookup rows in the other 3 partitions.

share|improve this answer
    
Thanks! My concern is around how the lookup works. In case, when there is no partition, when the lookup fetches the data from the data page for a particular key, would it do a seek or a scan to reach to this page. Would a direct key lookup perform better than a key lookup with partition pruning. –  Amam Apr 24 '13 at 18:42
    
The query plan may choose to scan on B and probe on A. –  Remus Rusanu Apr 24 '13 at 18:51
    
Thanks for the response Remus! i m assuming probe on A would mean lookup on A, but will the lookup itself do a seek to fetch the actual data from the data page ? –  Amam Apr 24 '13 at 18:57
    
@Amam A Key Lookup is always a seek. There is no partition elimination in your original example by the way - the query processor is just reporting that it touched 7 distinct partitions (3..9) while executing the query. None of the lookups accessed other partitions. –  Paul White Apr 30 '13 at 5:50
    
Thanks for the clarification Paul! –  Amam May 1 '13 at 6:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.