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?