Is it possible to expose the max depth of the B-Tree behind a rowstore index or a way to see how many nodes were traversed for a particular query?

Question

I'm personally interested in data structures and with knowing that rowstore indexes are stored with a B-Tree behind them, I'm always curious in trying to visualize what those B-Trees look like for a given table/index.

Is there a way to correlate or expose the number of nodes in a B-Tree for a given rowstore index via the execution plan, statistics, operator properties, index / table properties etc?

Furthermore can I do the same to determine the number of nodes traversed for a particular query?

Bonus points if there's a way to determine the number of leaf nodes in the B-Tree too.

Answer 1

Some of this information like index depth can be found in DMF dm_db_index_physical_stats(). Some outer interesting information that can be found in it is number of used data pages and the fragmentation level. Personally I have used the following query to get the state of the indexes for current database

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,  
ind.name AS IndexName, 
ixs.index_type_desc, 
ixs.index_depth, 
ixs.page_count, 
ixs.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ixs 
INNER JOIN sys.indexes ind 
ON ind.object_id = ixs.object_id AND ind.index_id = ixs.index_id;

More information about this DMF can be found here https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15

Answer 2

To find a B-Tree depth get an index_id from sys.indexes and run

DBCC IND(0 /*current db*/, 'your-table-name', 1 /*index_id*/)

Physically B-trees are stored within 8K pages like everything else in Sql Server. The result of the command above will list all the pages of your index with some useful information like IndexLevel and next/prev page ids. The maximum value of IndexLevel is the depth of your tree. Using the column it's also easy to calculate the number of leaves.