How does SQL Server know which disk cluster is allocated for a specific extent?

Question

Assume that we have a table with an unique clustered index. We query a row in that table using index key value. SQL Server will use index seek to find related data pages.

But how does SQL Server read those data pages from physical disk after having their file_id:page_id? How does it know the position of disk sector/cluster?

Answer 1

SQL Server does not deal with disk sector/cluster. It reads from files that are logically divided into pages (8Kb). Extent is 8 consecutive pages.

Every table has IAM page(s) associated with it where all the extents allocated to this table are listed (it's a bitmap page where 1 means extent is allocated to the object an 0 means it is not).

SQL Server maintains internal pointers to the first IAM page and the first data page of a heap. Those pointers can be found in the system view sys.system_internals_allocation_units.

In case of clustered index all the data lives in pages as usual, and it still can be read using IAM page(s), but it can be also accessed in "ordered" way using binary tree that is index.

This means that over the data (index leaf pages) server builds additional searching structure that also consists of pages, the root page directs you to lower index levels in base of clustered index key.

Conclusion: all addresses presented in the index pages consist of file_id:page_id, not of cluster/sector.

Here you can find how database structures are organized:

Inside Microsoft SQL Server 2008 T-SQL Querying