Menu
Since database data is organized in 8k pages in a B-tree, and likewise for PK information information, it should be possible for each table in the database to calculate the height of the B-Tree. Thus revealing how many jumps it takes to reach certain data.
Since both row size and PK size is of great importance, it is difficult to calculate since eg varchar(250) need not take up 250 bytes.
1) Is there a way to get the info out of SQL Server? 2) if not, is it possible to give a rough estimate using some code analyzing the tables of the db?
394
In the worst case the root contains only one key, and has two children. All other pages have b = ceiling(m/2)-1 keys and b children. -1. Hence, a B-tree with n keys has a height at most 1+ logb((n+1)/2).
If we cut the key size in half, then the depth of the B-tree goes from (log N)/log B to (log N)/log 2B (twice as many keys fit in the tree nodes), and that's just (log N)/(1+log B).
The Search Tree (B-Tree) Makes the Index Fast. The index leaf nodes are stored in an arbitrary order—the position on the disk does not correspond to the logical position according to the index order. It is like a telephone directory with shuffled pages.
this tree would have a minimum depth of 3, i.e. it would have a root node, one layer of non-leaf nodes, and the layer of leaf nodes. ... this tree would have a maximum depth of 3 as well.
YES! Of course!
Check out the DMV = dynamic management views in SQL Server - they contain a treasure trove of information about your indices. The dm_db_index_physical_stats is particularly useful for looking at index properties...
If you run this query in AdventureWorks against the largest table - Sales.SalesOrderDetails with over 200'000 rows - you'll get some data:
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')
You'll get output for all index levels - so you'll see at one glance how many levels there are in the index (I have three rows -> three levels in the index). Index Level 0 is always the leaf level - where in the clustered index (index_id = 1) you have your actual data pages.
You can see the average, minimum and maximum record sizes in byte and a great deal of additional info - read up on DMV's, there's a great way to diagnose and peek into the inner workings of SQL Server!
58
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
