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?
Best Answer
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.