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.


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

ind.name AS IndexName, 
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.