Finding the height of a B-tree table in SQL Server

Question

Since the database data is organized on 8k pages in the B-tree, as well as for information PK-information, each table in the database should be able to calculate the height of the B-Tree. Thus, it is shown how many jumps are required to achieve certain data.

Since the row size and PK size is of great importance, it is difficult to calculate, since, for example, varchar(250)250 bytes are not required.

1) Is there a way to get information from SQL Server? 2) if not, is it possible to give a rough estimate using some code analyzing db tables?

Answer 1

YES! Of course!

DMV = SQL Server - . dm_db_index_physical_stats ...

AdventureWorks - Sales.SalesOrderDetails 200 000 - :

SELECT 
    index_depth,
    index_level,
    record_count,
    avg_page_space_used_in_percent,
    min_record_size_in_bytes,
    max_record_size_in_bytes,
    avg_record_size_in_bytes
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Sales.SalesOrderDetail'), 1, NULL, 'DETAILED')

- , ( → ). 0 - (index_id = 1) .

, ​​ - DMV, SQL Server!

Answer 2

:

SELECT INDEXPROPERTY(OBJECT_ID('table_name'), 'index_name', 'IndexDepth')

Answer 3

from How many elements can be stored in a B-tree of order n?

 (Average Number of elements that fit in one Leaf-node) * n ^ (depth - 1)