You will love DM function sys.dm_db_index_physical_stats.

In DETAILED mode it gives you info about the index, level-by-level (0 = leaf level).

Example:

-- Create HEAP table and insert 10M rows in one step

select top 10000000 a.*

INTO BigTable

from sys.all_columns a cross join sys.all_columns b

-- create clustered index (table becomes "clustered", it is no longer HEAP)

alter table BigTable add id int identity primary key clustered

-- display index physcal details, level by level

select *

from sys.dm_db_index_physical_stats

(db_id(), -- current db

object_id('BigTable'),

1, -- clustered index

DEFAULT, -- default = all partitions

'DETAILED' -- DETAILED (gives info by each index level), SAMPLED, LIMITED (fastest)

)

-- cleanup

-- drop table BigTable

And the result is:

index_levelpage_countrecord_count

010551510000000

1170105515

21170

On leaf level (level 0) we have 10 000 000 rows in 105 515 data pages.

On first non-leaf level (level 1) we have 105 515 index ROWS that fit in only 170 index PAGES.

Above that, at level 2 is only one, a root page of the index containing 170 index rows (each index row points to a PAGE of the level below, so at level 1 we have 170 PAGES).

Percent of Btree pages in total pages is (170+1) / (170+1+105515) = 0,16% which is well below 1%.

Hope that helps a bit in understanding.