tree - How does the SQL Server engine load data in memory based on Clustered Index? -

we know clustered index struct row data b tree. root node , internal nodes store cluster key , data address reference other columns.(call key nodes)

the leaf nodes store datas.(call data nodes) question comes, what's progress of db load data? load key nodes first. , engine match parameters key value. if find key node. use data address load datas physical disks immediately?

or load entire tree first? guess may cause bad performance.

thank you advice!

this depends on operation sql server clustered index.

on index seek, traverses b-tree root node node containing key being searched, , leaf node (if required).

on index scan, traverses leaf nodes horizontally (each leaf node contains pointers next , previous leaf node).

either way first tries find each page in buffer pool (this called logical read) and, failing that, retrieves disk (this called physical read).

if enable io statistics issuing

set statistics io on 

prior running query, see how many logical , physical reads each table query does.