1

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

My concern is around how the lookup works. In case, when there is no partitioning, 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?

1

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

The query plan may choose to scan on B and probe (lookup) on A.

A Key Lookup is always a seek into the clustered index. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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