I've made up my case scenario.
The index take around 450 megs.
I've build up a specific query to make sure an index seek occurs (which was confirmed by the plan).
Upon check, the index was taking 108 megs in memory, a quarter of the index when only 8 rows were returned (it is a non unique index).
Here's the index definition if you'd like:
CREATE NONCLUSTERED INDEX [NameOfIndex]
INCLUDE ( [Col4])
Col1, 2, 3 are INT, Col4 is a DECIMAL (18,6)
I will need to check the index BTree depth and compute how many pages are require to be read to get through the leaf level (intermediate pages should be able to store 670 entries (671 - 1)). But I believe it (BTree depth) should be quite small. Hence why it is taking 108 megs for that part of the index?
FYI: The index is newly created with 100% Fill Factor. Pad_index off for near maximum compaction.