Sql-server – 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

btreeindexsql serversql-server-2016tree

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.

Best Answer

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://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15

Related Question